Până acum, dacă ați citit postările mele anterioare din serie Ghidul definitiv pentru Unpivot în Excel (toate postările aici), vă veți simți încrezători în următoarea provocare: Cum să împărțiți valorile separate prin virgule în Power Query.

Avem un tabel cu nume de evenimente, date și nume de participanți separați de virgule și dorim să construim un tabel pivot care să ne arate participanții și numărul lor de evenimente (așa cum arată următoarea captură de ecran).

divizați

În această postare vom examina soluția intuitivă care include o transformare divizată și unpivot, dar apoi vom vedea o modalitate mult mai bună și scalabilă de a ne atinge obiectivul.

Să începem cu o prezentare generală a soluției intuitive.

Deschideți acest registru de lucru, selectați orice celulă din Tabelul 1, și faceți clic pe Din tabel în Obțineți și transformați secțiunea din Date filă în Excel 2016 (Sau dacă utilizați Excel 2010 sau 2013 faceți clic pe Din tabel în Interogare de alimentare, după ce ați instalat programul de completare).

Se va deschide Editorul de interogări. Faceți clic dreapta pe Participanți coloană, selectați Coloană divizată, și apoi faceți clic pe De Delimiter ...

În Split Column de Delimiter dialog, selectați Virgulă ca delimitator.

Acum puteți da clic O.K, dar vă încurajez să extindeți Opțiuni avansate înainte de a părăsi acest dialog.

Actualizați: Acest articol a fost scris înainte ca Microsoft să introducă opțiunea Split Into Rows. Se recomandă utilizarea acestuia. După ce o faceți, nu va mai trebui să efectuați unpivot sau să aveți grijă de valorile lipsă. Puteți continua să citiți, dacă opțiunea Împărțiți în rânduri nu este relevantă pentru provocarea dvs. de date și doriți în continuare să găsiți o modalitate de a împărți coloana în coloane într-un mod robust.

Puteți vedea că Power Query propune să împărțiți Participanți coloană în 7 coloane. De ce doar 7? Deoarece Editorul de interogări a identificat maximum 7 valori separate prin virgulă în previzualizare. Până acum puteți ghici că întregul set de date este, de obicei, mai mare decât previzualizarea, deci putem întâlni cazuri în care avem mai mult de 7 valori separate prin virgulă. OK, să facem clic O.K.

Acum avem 7 coloane: Participanți.1 la Participanți.7, și putem efectua transformarea unpivot.

Selectați coloane Eveniment și Data, faceți clic dreapta pe unul dintre antetele lor și selectați Dezimpivotați alte coloane.

Acum putem elimina coloana Atribut și schimbați numele coloanei Valoare la Participant. Putem schimba și tipul de Data până în prezent.

Asta este. Am ajuns la formatul dorit de Eveniment, Data și Participant. Dar va funcționa bine?

Amintiți-vă că am văzut numărul 7 care a fost folosit pentru a determina câte coloane să împărțiți?

Ei bine, dacă acest număr este codat în etapele de transformare, cu siguranță vom rata participanții la celule care conțin mai mult de 7 valori separate prin virgulă.

Hai să verificăm.

În Acasă fila, faceți clic pe Editor avansat.

Putem vedea în expresia M că împărțirea a fost realizată în 7 coloane codificate.

Ar fi grozav dacă ați putea schimba numărul de la 7 la, să zicem 10, să împărțiți celulele cu mai mult de 7 valori separate prin virgulă?

Răspunsul este că poți. Doar înlocuiți al treilea parametru al funcției Table.SplitColumn în linia care începe cu # ”Split Column by Delimiter” si foloseste 10, în locul listei „Participanți.1”,…, „Participanți.7”.

De asemenea, ar trebui să eliminați următoarea linie care funcționează Table.TransformColumnTypes această funcție folosește și cele 7 nume de coloane codificate.

OK, avem acum o soluție mai bună care presupune că nu pot fi utilizate mai mult de 10 valori separate prin virgulă în coloană Participanți.

Dar, după cum puteți verifica, registrul de lucru pe care îl folosim în această prezentare generală are mai mult de 10 participanți separați de virgule. Următorul lucru pe care l-ai crede este să folosești un număr suficient de mare. Poate 100?

Ei bine, această abordare nu ne va conduce la o transformare eficientă, deoarece vom pierde prea multă memorie și CPU în transformarea multor coloane cu valoare nulă. În plus, dacă presupunerea noastră de a folosi numărul 100 va fi greșită?

Split & Expand - Cea mai bună abordare

Este timpul să vedem cea mai bună abordare pentru a rezolva problema noastră.

Să începem din nou și să importăm Tabelul 1 către Editorul de interogări.

Acum vom face clic dreapta pe coloană Participanți, Selectați Transforma și faceți clic pe minuscule (Nu vom aplica o transformare cu litere mici aici, este doar o scurtătură pentru a genera o parte din formula de care vom avea nevoie).

În acest stadiu, dacă nu ați activat bara de formule, este momentul să faceți acest lucru. Doar verificați Bara de Formula cutie în Vedere filă.

Editați formula și înlocuiți-o Text.Mai jos cu Splitter.SplitTextByDelimiter („,”)

presa introduce după ce aplicați modificarea și veți vedea că coloana Participanți s-a transformat într-o listă.

Acum respirați adânc și pregătiți-vă să vedeți magia în acțiune - Gata? Faceți clic pe butonul de extindere (butonul mic din antetul coloanei Participanți).

A fost amuzant. Am reușit să ajungem la tabelul dorit fără a utiliza coloane pentru valorile împărțite și fără pasul de nepivotare.

Putem chiar curăța formula (dar acest lucru nu este necesar pentru ao face să funcționeze) și să eliminăm toate mențiunile despre# „Text cu litere mici” si foloseste SplitParticipants in schimb.

Iată expresia rezultatului M:

Asta este. Acum puteți face clic Închideți și încărcați în Acasă și creați un tabel pivot pentru fiecare dintre cele trei interogări menționate.

Puteți selecta a doua foaie de lucru din registrul nostru de lucru și puteți vedea cum numai cel de-al treilea tabel este corect în obținerea tuturor participanților.