Tutorial Microsoft Power Query despre cum să remediați problemele legate de lățimea coloanelor fixe mixte

Mudassir: Pentru astăzi, avem o problemă foarte interesantă cu care să lucrăm. Problema cu acest fișier este că este rezolvat delimitat de coloane și nu știu cum să rezolv acest lucru folosind Microsoft Power Query. Puteți viziona videoclipul complet al acestui tutorial în partea de jos a acestui blog.

În primul rând, nu mi-a fost ușor să șterg coloanele în mod dinamic. În al doilea rând, în acest raport, avem un tabel cu o lățime de coloană diferită și apoi un alt tabel cu o lățime de coloană diferită.

Deci, dacă aplic un delimitator dinamic în partea de sus, nu aș putea obține datele corect. Vreau să obțin toate aceste date din al doilea tabel și numerele mele de produse din primul tabel. De asemenea, vreau numărul locului de muncă în fiecare rând din toate tabelele.

Am încercat să o rezolv pe cont propriu, dar din moment ce asta are legătură cu interogarea puterii, am avut nevoie de ajutorul Melissa. Am crezut că îi va lua cel puțin două zile, dar a reușit să vină imediat cu o soluție.

Melissa ne va arăta cum a rezolvat această problemă complicată. Cred că majoritatea oamenilor se vor confrunta cu acest tip de probleme și caută modalități de a le rezolva.

Melissa: Primul sfat este că dacă te uiți la un fișier cu lungime fixă, poți să mergi la fila Vizualizare și să activezi opțiunea Monospațiată .

Putem vedea că este un font cu lungime fixă. Putem vedea, de asemenea, anteturile, tabelele inițiale și subtabele. Acestea sunt părțile care ne interesează și din care dorim să le extragem.

De asemenea, asigurați-vă că aveți bara de formule activată. Este întotdeauna un lucru bun să îl aveți vizibil pe ecran, deoarece îl vom folosi frecvent pentru a face modificări ușoare la intrare.

Am creat un parametru pentru locația fișierului în care am stocat fișierul CSV. L-am adus ca fișier provizoriu și l-am introdus în parametrul meu de locație a fișierului. Apoi am creat o referință și voi lucra de la acea referință. Așa că la asta ne uităm acum în interogarea de putere Microsoft.

Cuprins

• Adăugarea unei coloane de index
• Crearea unei liste de buffer în Microsoft Power Query
• Împărțirea antetelor din rânduri
• Eliminarea elementelor nule și necompletate din Microsoft Power Query
• Împărțirea Secțiunilor
• Tăierea șirurilor de text în Microsoft Power Query
• Concluzie

Adăugarea unei coloane de index

De obicei, când încep să lucrez la un astfel de fișier, va trebui să cunosc cerințele clientului. Întreb de ce are nevoie clientul și ce să caute.

În acest caz, dorim numărul articolului și numărul jobului din anteturi, apoi vrem toate detaliile care aparțin acelui antet specific.

Vom avea nevoie de o cheie pentru a aduce aceste lucruri înapoi împreună. Dar dacă nu există nicio cheie prezentă, atunci trebuie să adaug o coloană index . Voi face clic pe pictograma mini-tabel, voi selecta Adăugați o coloană index , apoi voi adăuga De la 0 .

Voi folosi o logică mai târziu cu liste, care au indexul bazat pe 0. Dacă indexul începe de la zero înseamnă de fapt că puteți face referire la același rând. În caz contrar, va trebui să scazi 1 pentru a ajunge la acea poziție bazată pe 0.

Apoi, trebuie să găsim poziția în care se află anteturile noastre, ceea ce putem face destul de ușor, deoarece acele anteturi se repetă constant în întregul fișier.

Pentru a începe, să copiem această valoare:

Adăugați o nouă interogare goală, inserați-o și numiți-o HeaderID .

Voi face același proces pentru subtabele. Voi copia acel șir de text, voi crea o altă interogare goală și voi lipi acea valoare. Acesta va fi șirul pe care îl vom folosi când căutăm rânduri detaliate.

Dacă acest proces modifică cumva antetul pentru oricare dintre aceste tabele, tot ce trebuie să fac este să schimb unul dintre șirurile de text și fișierul va funcționa din nou.

Nu trebuie să mă scufund în codul M pentru a căuta acel șir pe care îl căutăm. Putem folosi asta doar ca parametru.

Să activăm încărcarea pentru aceste două interogări.

Crearea unei liste de buffer în Microsoft Power Query

Primul lucru pe care îl voi face este să transform Column1 într-o listă prin referință și încărcare în memorie o dată. În acest fel, nu trebuie să fac apeluri repetate la fișier.

Voi deschide editorul avansat și îl voi plasa până la capăt în partea de sus. Când utilizați interfața cu utilizatorul pentru a vă construi codul, acesta va face referire la pasul anterior.

Când plasați un pas de tampon în altă parte în cod și doriți să faceți o modificare, mai târziu, vă va ajuta să faceți modificările pasului pe care îl creați manual.

Voi numi această BufferList și voi face referire la Column1. Pentru a-l încărca în memorie, voi adăuga un pas List.Buffer .

Iată variabila mea în partea de sus. Îl pot referi din nou și din nou.

Primul lucru pe care vreau să-l determin este de unde încep anteturile mele, deoarece am nevoie de o cheie pentru a păstra aceste secțiuni de antet și pentru a obține o singură valoare pentru toate aceste rânduri. Pentru a face acest lucru, voi adăuga o coloană personalizată și o voi numi Antet .

Voi scrie că, dacă Coloana1 este egală cu ID-ul antet, atunci vreau ca numărul meu de index să fie nul.

Drept urmare, a găsit textul și a returnat 5 și 23.

Am nevoie de această valoare pe toate rândurile, așa că trebuie să o completez în jos. Puteți doar să faceți clic dreapta pentru a completa, dar puteți utiliza și o sintaxă foarte simplă și o puteți adăuga în bara de formule.

În acest caz, am adăugat Table.FillDown și în șirul de text am indicat ce coloană vrem să umplem în jos (Header).

Acum, am completat-o ​​pentru toate rândurile. Avem o cheie pentru toate secțiunile antetului și toate secțiunile de rând, deoarece toate împărtășesc această valoare.

Împărțirea antetelor din rânduri

Următorul pas este împărțirea antetelor din rânduri. Voi adăuga o altă coloană personalizată și o voi numi Temp . De data aceasta, vom face ceva mai elaborat și vom folosi acea BufferList pe care am creat-o mai devreme.

Vom folosi câteva funcții de liste pentru a analiza fiecare dintre poziții și pentru a afla dacă există o potrivire cu indexul.

Voi începe cu o instrucțiune if și voi folosi List.Contains pentru a căuta o anumită poziție în BufferList și a face referire la interogarea HeaderID .

Vrem să-l găsim pe toată lungimea fișierului și apoi să returnăm poziția articolului în listă. Dacă se potrivește cu indexul, avem o potrivire pentru acel rând specific.

Apoi vreau să returnez o valoare pentru a identifica antetul. În acest caz, doar returnez un H. Voi copia sintaxa, astfel încât să nu fie nevoie să o scriu din nou.

De asemenea, trebuie să identificăm secțiunea de rând. Dacă lista nu conține HeaderID , ci DetailID , atunci ne aflăm într-o secțiune de rând.

Dacă Column1 este un șir de text gol, atunci vreau să rămână gol . Dacă nu este cazul, atunci vreau să fie nul .

Această opoziție a primit rândul antetului și a returnat un H, apoi a găsit un rând detaliat și a returnat un R. Apoi a returnat 0 pentru toate elementele care sunt partajate în acea secțiune de rând.

Aceste spații libere sau nule sunt importante deoarece vă permit să completați. Umplerea în jos nu se va deplasa prin acele celule goale, astfel încât să le putem elimina mai târziu.

Vom face acest lucru în bara de formule și vom folosi din nou Table.FillDown . Vrea o listă cu numele coloanei, care este coloana noastră Temp .

Acum avem valorile H și R repetate pe toată această coloană, ceea ce înseamnă că putem de fapt împărți anteturile din secțiunile detaliate.

De asemenea, îl puteți completa din interfața cu utilizatorul dacă nu doriți să scrieți codul. Puteți doar să faceți clic dreapta și să selectați Umplere , apoi Jos .

Eliminarea elementelor nule și necompletate din Microsoft Power Query

Acum că avem acest drept, putem elimina lucrurile de care nu avem nevoie. Tot ceea ce este nul sau conține un spațiu liber sunt rândurile de care nu avem nevoie și trebuie eliminate. Le putem elimina prin filtrare.

Împărțirea Secțiunilor

Odată ce eliminăm acele spații libere și nule, rămânem cu tot ce avem nevoie. În acest moment, putem împărți doar secțiunile. Ne putem concentra pe rândurile antetului și le alegem pentru că au o spațiere separată față de toate rândurile de detalii (care au și o spațiere separată).

Voi adăuga un nou pas în bara de formule care îmi permite să creez un alt filtru pe aceeași coloană. În acest caz, voi păstra doar toate secțiunile antetului.

Acum, am toate acele rânduri de antet aici.

Pot să selectez Coloana1 , să merg la bara de formule, să selectez Divizarea coloană , apoi să divizez pe poziții.

Apoi, interogați-l pe putere să-l descopere pe acesta. Va sugera câteva poziții. Faceți clic pe OK pentru a accepta acele poziții.

Singurele lucruri care ne interesează din anteturi sunt articolul și numărul locului de muncă .

În bara de formule de aici, le pot redenumi cu Item și Job # . Acest lucru mă va salva de la un alt pas de redenumire a coloanei.

După acest pas, tot ce trebuie să fac este să selectez Itemul , să selectez Job # și, bineînțeles, să selectez cheia antet . Apoi voi elimina toate celelalte coloane pentru că nu mai am nevoie de acestea.

Acesta va fi rezultatul. Încă trebuie să curățăm valorile și să eliminăm elementul de text și liniuțele. Tot ce ne dorim sunt acele valori între ele.

Așa că deschidem acest lucru și deselectăm liniuțele și elementele.

Acum, toate anteturile sunt gata.

Trebuie să facem același proces și pentru DetailID . Va trebui să redenumesc acești pași, astfel încât să-mi fie mai ușor să revin la ei puțin mai târziu.

Vom reveni la interogarea inițială pe care am început-o. Am început cu Filtered Rows în panoul Applied Steps.

Voi copia asta și o voi adăuga la filtrul meu. De data aceasta, nu selectez H, ci selectez R.

Apoi voi selecta Coloana 1, voi merge la coloana Divizat, împărțit pe poziții, apoi voi pune interogarea de putere să o rezolve.

Acesta este ceea ce sugerează Power Query. Să încercăm asta.

Asta chiar arată destul de bine. Chiar și rândurile totale s-au împărțit perfect. Desigur, există o mulțime de spații pentru că am avut acea indentare.

Tăierea șirurilor de text în Microsoft Power Query

Voi selecta prima coloană, apoi apăsați în jos + Shift pentru a selecta până la coloana 1.10. Accesați Transformare , selectați Format , apoi Decupați . Tăierea va elimina doar spațiile în exces din față sau de la capătul șirului, nu între ele.

Apoi, putem doar promova anteturile, așa că nu trebuie să introduc toate anteturile sau titlurile acestor coloane. În pasul împărțit, am redenumit două coloane. Acum, bineînțeles, cu 10 coloane, asta e un pic deranjant.

Trebuie să scăpăm și de acele valori în exces. Deoarece avem totaluri, trebuie să folosesc una dintre ultimele trei coloane pentru că sunt singurele rânduri care au valori suplimentare undeva la mijloc. Apoi vom deselecta acele spații libere, liniuțe și texte.

Apoi voi elimina coloanele inutile, astfel încât tot ce rămâne este un tabel cu doar anteturi și doar detalii. Avem nevoie de o cheie pentru a aduce acele secțiuni din nou împreună.

Pentru aceasta, putem folosi o auto-imbinare, astfel încât să putem îmbina tabelul cu el însuși pentru a aduce informațiile înapoi împreună. În fila Acasă , selectați Îmbinare , apoi selectați Coloana 5 și aceeași interogare.

În loc de AllDetails, vreau ca AllHeaders să fie tabelul meu inițial cu care vreau să fuzionez.

Acest lucru a adus înapoi toate informațiile din tabelul antet cu un singur rând pentru fiecare articol și fiecare număr de job.

Am folosit o cheie pentru a fuziona cu rândurile de detalii. Dacă apăs în lateral în spațiul alb de aici, vom vedea o previzualizare a tuturor rândurilor care aparțin antetului 5.

Vom elimina ultima coloană aici și apoi am terminat de reparat lățimea fixă ​​mixtă a coloanei în interogarea de putere Microsoft.

Concluzie

În acest tutorial, am venit cu o modalitate de a rezolva problemele mixte de lățime fixă ​​a coloanei folosind Microsoft Power Query. Dacă v-a plăcut conținutul prezentat în acest tutorial special, vă rugăm să nu uitați să vă abonați la canalul TV LuckyTemplates.

Avem o cantitate imensă de conținut care iese tot timpul de la mine și de la o serie de creatori de conținut, toți dedicați îmbunătățirii modului în care utilizați LuckyTemplates și Power Platform.

Melissa