Tabel de dimensiuni într-un fișier text: soluție Power Query

În această postare pe blog, voi trece peste problema nr. 2 din problema săptămânii în curs de desfășurare pe , unde am discutat despre cum să sortăm datele dezordonate într-un tabel cu dimensiuni curate. Aceasta este a doua iterație a acestei noi inițiative pe care o găzduim la LuckyTemplates. Sunt pasionat în special de această serie, deoarece oferă tuturor șansa de a obține mai multă practică în mod regulat. Puteți viziona videoclipul complet al acestui tutorial în partea de jos a acestui blog.

În prima miercuri a fiecărei luni, există o provocare DAX , iar în a treia miercuri, există o provocare de interogare de putere .

Aceasta este o oportunitate excelentă de a explora, descoperi și învăța lucruri noi despre aceste limbi pe care trebuie să le folosești în LuckyTemplates.

Veți găsi o categorie pe problema săptămânii pe forumul LuckyTemplates.

Accentul principal este totul pe proces și nu atât pe rezultat.

Dacă reușiți să descompuneți o problemă în bucăți mai mici, veți putea rezolva orice problemă cu care vă confruntați.

Melissa de Korte

Brian a vorbit anterior despre o tehnică numită depanare a rațelor de cauciuc . Dacă ai ratat asta, asigură-te că îi vezi videoclipul. Vă poate ajuta atunci când rămâneți blocat în aceste provocări.

În primul rând, să examinăm sarcina la îndemână. Avem un fișier text dezordonat pe care trebuie să îl transformăm într-un tabel de dimensiuni adecvat.

Acum, când mă uit la astfel de date, în cea mai mare parte, totul va fi despre curățarea textului, eliminarea caracterelor nedorite, tăierea, scrierea cu majuscule și așa mai departe.

Dar, de asemenea, trebuie să recuperăm toate rândurile din această stivă de date și să le transformăm într-o singură linie pentru fiecare țară. Îi spun date stiva deoarece toate numele câmpurilor sunt repetate într-o singură coloană pentru fiecare țară.

Am ceva experiență în Excel și una dintre cele mai puternice caracteristici ale sale din punctul meu de vedere este tabelele pivot .

Cuprins

• Tabelele pivot în Excel
• Utilizarea Power Query pentru a crea un tabel de dimensiuni
• Funcție personalizată de curățare a textului
• Pivotarea Datelor
• Concluzie

Tabelele pivot în Excel

Tabelele pivot îmi permit să văd datele segment cu segment. În funcție de ceea ce plasați în secțiunea de rând, un tabel pivot condensează fiecare apariție a acelui segment într-o singură linie.

Puteți apoi să o împărțiți și mai mult trăgând câmpurile în secțiunea coloanei.

Am creat un exemplu despre cum arată asta. Aici, avem aproape aceleași date pe care le aveam în fișierul text.

Și pe pagina următoare, am creat un tabel pivot gol.

Acum, ceea ce nu ți-am arătat este că am trei coloane aici în loc de cele două pe care ți le-am arătat deja.

O să-mi trag segmentul pe rânduri.

Deoarece Coloana 1 avea numele câmpurilor, o voi trage în secțiunea coloanei.

Coloana 2 a avut toate valorile câmpului, așa că o voi trage în secțiunea de valori.

Putem vedea aici că tabelele pivot nu pot gestiona șiruri de text. Le numără, dar arată că avem o singură valoare pentru fiecare câmp.

Acum să aruncăm o privire la acel segment pe care l-am creat. Așa că mă voi întoarce la Date și îmi voi ascunde coloana. Puteți vedea că este doar un număr index, identificând fiecare dintre blocurile separate de date care sunt încă stivuite unul peste altul.

Deci, pentru mine, transformarea cheie va fi un pivot al datelor pentru a le aduce înapoi în acel format tabelar.

Pe forum, am văzut și alte moduri de a rezolva asta. Există și alte modalități de a ajunge la un format tabelar în afară de utilizarea unei acțiuni pivot și funcționează la fel de bine. Dacă ești interesat de asta, mergi pe forum și începe să explorezi.

Utilizarea Power Query pentru a crea un tabel de dimensiuni

Să trecem peste interogarea de putere și să revizuim soluția mea.

Personal, cred că UI face o treabă grozavă de a scrie cea mai mare parte a codului M pentru noi. Așa că am tendința de a-mi proiecta interogările folosind interfața cu utilizatorul cât de mult pot.

Odată ce interogarea face ceea ce trebuia să facă, voi intra în editorul avansat și voi examina codul M pentru a vedea dacă îl pot modifica. Să vedem cum arată.

Acesta este grupul meu de bază care este construit folosind interfața cu utilizatorul.

Puteți vedea că există o mulțime de pași în pașii aplicați din partea dreaptă.

Acum, asta în sine nu este o problemă, dar doar privind acești pași, puteți vedea că există o mulțime de transformări care pot fi grupate.

Să deschidem editorul avansat.

Vedem că această interogare are 31 de pași.

Am adăugat și câteva comentarii la aceeași interogare care conține cei 31 de pași, dar am împărțit-o în secțiuni.

Primul lucru pe care l-am făcut a fost să elimin pasul de tip de modificare. Vă sugerez să creați o funcție personalizată pentru a face toți acești pași de transformare a textului.

Există o mulțime de comentarii aici, dar sunt doar două lucruri pe care vreau să le subliniez în această postare pe blog. Prima este funcția personalizată pentru curățarea textului .

Al doilea este pasul pivot pentru a transforma acest tip de date înapoi într-un format tabelar adecvat .

Funcție personalizată de curățare a textului

Să ne întoarcem la primele etape ale construirii interogării mele, unde aveam toți acești pași grupați pentru a curăța aceste texte: coloana 1 și coloana îmbinată.

Am adăugat și o coloană personalizată suplimentară. Singurul său scop este de a construi funcția mea personalizată de curățare a textului . Am invocat asta pe coloana îmbinată.

În acest fel, nu trebuie să scriu funcția dintr-o singură mișcare, ci să o construiesc treptat, pe rând, adăugând o nouă transformare după revizuirea rezultatelor de la pasul anterior.

Să ne uităm la codul M pentru funcția de curățare a textului.

După cum puteți vedea, am mai mulți pași. Când construiam această funcție de text, am mers înainte și înapoi între interogare pentru a vedea rezultatele pentru a vedea ce să construiesc și ce să corectez în continuare.

Cu acest rezultat, am efectuat toate transformările de care aveam nevoie. Unele dintre funcțiile M pe care le-am folosit aici au fost furnizate de interfața cu utilizatorul când am creat interogarea mea inițială, cum ar fi Textul. Funcția de tăiere. Cu toate acestea, celelalte funcții utilizate nu au fost.

Dacă nu sunteți familiarizat cu ele, puteți căuta online toate funcțiile M din ghidul de formule M. Acesta este linkul unde trebuie să mergi.

Veți găsi o secțiune dedicată interogării puterii și funcțiilor.

Dacă derulați în jos, veți găsi secțiunea despre funcția text și fiecare secțiune începe cu o prezentare generală. Există o listă cu toate funcțiile text din limbajul de interogare de putere și formula M.

Dacă sunteți în căutarea unei anumite transformări, o puteți căuta chiar aici.

Pivotarea Datelor

A doua parte pe care am vrut să o evidențiez în timpul creării acestui tabel de dimensiuni este pivotarea datelor în sine. Să aruncăm o privire mai atentă și la asta.

Am început prin a adăuga un index. Am actualizat acel index pentru a segmenta corect blocurile de date. Am făcut acest lucru returnând numărul de index pentru fiecare rând în care avea țara textului în coloana 1, apoi am completat acea valoare în jos.

Tot ce trebuie să facem este să pivotăm datele în sine. În fila de transformare, veți găsi coloana Pivot. Cu Coloana 1 selectată, faceți clic pe Pivot Column.

Va folosi valorile coloanei 1 ca nou nume de coloană. De asemenea, vrea să știe unde sunt valorile pentru acele nume de câmp. Acestea sunt în coloana noastră combinată.

Dacă Excel se poate ocupa de valorile text, Power Query o poate face și datorită setărilor sale avansate de opțiuni. Tot ce trebuie să facem este să selectăm Nu agregați pentru ca acesta să poată gestiona valorile text.

Odată ce facem clic pe OK, putem vedea că datele noastre au fost pivotate.

Concluzie

Imaginea de mai sus este interogarea rezultatului final. Sper că v-a plăcut cum am remodelat datele dezordonate din fișierul text furnizat și le-am transformat într-un tabel de dimensiuni curat , potrivit pentru analiză.

Dacă v-a plăcut această postare pe blog, vă rugăm să abonați-vă la canalul LuckyTemplates pentru a nu pierde niciun conținut nou.

Sper să vă văd pe toți în viitoarele provocări Problema săptămânii.

Melissa