Microsoft Power Query-veiledning om hvordan du løser problemer med blandede faste kolonnebredder

Mudassir: For i dag har vi et veldig interessant problem å jobbe med. Problemet med denne filen er at den er løst avgrenset av kolonner, og jeg vet ikke hvordan jeg skal løse dette ved å bruke Microsoft Power Query. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

For det første var det ikke lett for meg å slette kolonnene dynamisk. For det andre, i denne rapporten har vi én tabell med en annen kolonnebredde, og deretter en annen tabell med en annen kolonnebredde.

Så hvis jeg bruker ett skilletegn dynamisk på toppen, ville jeg ikke være i stand til å få dataene pent. Jeg ønsker å hente alle disse dataene fra den andre tabellen, og produktnumrene mine fra den første tabellen. Jeg vil også ha jobbnummeret i hver eneste rad i alle tabellene.

Jeg prøvde å løse det på egen hånd, men siden dette har noe med strømspørring å gjøre, trengte jeg hjelp fra Melissa. Jeg trodde at det ville ta henne minst to dager, men hun klarte å komme med en løsning med en gang.

Melissa skal vise oss hvordan hun løste dette kompliserte problemet. Jeg tror de fleste vil håndtere denne typen problemer og leter etter måter å løse dem på.

Melissa: Det første tipset er at hvis du ser på en fil med fast lengde, kan du gå til fanen Vis og slå på alternativet Monospaced .

Vi kan se at det er en font med fast lengde. Vi kan også se overskrifter, innledende tabeller og undertabeller. Dette er delene vi er interessert i og ønsker å trekke ut fra.

Sørg også for å ha formellinjen aktivert. Det er alltid en god ting å ha den synlig på skjermen din, siden vi kommer til å bruke den ofte for å gjøre små endringer i inngangen.

Jeg har opprettet en parameter for filplasseringen der jeg har lagret CSV-filen. Jeg tok den inn som en iscenesettelsesfil og satte den inn i filplasseringsparameteren min. Deretter opprettet jeg en referanse, og jeg vil jobbe fra den referansen. Så det er det vi ser på akkurat nå i Microsoft Power Query.

Innholdsfortegnelse

• Legge til en indekskolonne
• Opprette en bufferliste i Microsoft Power Query
• Deler overskrifter fra radene
• Fjerning av null- og blanktegn i Microsoft Power Query
• Splitting av seksjonene
• Trimming av tekststrengene i Microsoft Power Query
• Konklusjon

Legge til en indekskolonne

Vanligvis, når jeg begynner å jobbe med en fil som denne, må jeg kjenne kundens krav. Jeg spør hva kunden trenger, og hva man skal se etter.

I dette tilfellet vil vi ha varenummeret og jobbnummeret fra overskriftene, og så vil vi ha alle detaljene som hører til den spesifikke overskriften.

Vi trenger en nøkkel for å bringe disse tingene sammen igjen. Men hvis det ikke er noen nøkkel til stede, så er det jeg bør legge til en . Jeg klikker på minitabellikonet, velger Legg til indekskolonne og legger til Fra 0 .

Jeg skal bruke en logikk senere med lister, som har den 0-baserte indeksen. Å la indeksen starte fra null betyr faktisk at du kan referere til den samme raden. Ellers må du trekke fra 1 for å komme til den 0-baserte posisjonen.

Deretter må vi finne posisjonen der overskriftene våre er, noe vi kan gjøre ganske enkelt fordi disse overskriftene gjentas konstant gjennom hele filen.

For å starte, la oss kopiere denne verdien:

Legg til et nytt tomt søk, lim det inn, og kall dette HeaderID .

Jeg vil gjøre den samme prosessen for undertabellene. Jeg kopierer den tekststrengen, lager et nytt tomt søk og limer inn verdien. Dette kommer til å være strengen vi skal bruke når vi ser etter detaljerte rader.

Hvis denne prosessen på en eller annen måte endrer overskriften for noen av disse tabellene, er alt jeg trenger å gjøre å endre en av tekststrengene og filen vil fungere igjen.

Jeg trenger egentlig ikke å dykke ned i M-koden for å søke etter den strengen vi leter etter. Vi kan bare bruke dette som en parameter.

La oss aktivere belastningen for disse to spørringene.

Opprette en bufferliste i Microsoft Power Query

Det første jeg skal gjøre er å gjøre Kolonne1 til en liste ved å referere og laste den inn i minnet én gang. På denne måten slipper jeg å foreta gjentatte anrop til filen.

Jeg åpner den avanserte editoren og plasserer den helt øverst. Når du bruker brukergrensesnittet til å bygge koden din, vil den referere til forrige trinn.

Når du plasserer et buffertrinn et annet sted i koden din og du ønsker å gjøre en endring, vil det senere hjelpe deg med å gjøre endringene i trinnet du oppretter manuelt.

Jeg kaller denne BufferList og refererer til Column1. For å laste den inn i minnet, legger jeg til et List.Buffer- trinn.

Her er variabelen min helt øverst. Jeg kan referere det om og om igjen.

Det første jeg vil finne ut er hvor overskriftene mine starter fordi jeg trenger en nøkkel for å beholde disse overskriftsdelene og få en enkelt verdi for alle disse radene. For å gjøre dette legger jeg til en egendefinert kolonne og kaller den Header .

Jeg vil skrive at hvis Kolonne1 er lik vår Header ID, vil jeg at indeksnummeret mitt skal være null.

Som et resultat fant den teksten og returnerte 5 og 23.

Jeg trenger den verdien på tvers av alle radene, så jeg må fylle den ned. Du kan bare høyreklikke for å fylle ut, men du kan også bruke veldig enkel syntaks og legge det til i formellinjen.

I dette tilfellet la jeg til Table.FillDown og i tekststrengen indikerte jeg hvilken kolonne vi ønsker å fylle ned (Header).

Nå har vi fylt det ned for alle radene. Vi har en nøkkel for alle overskriftsseksjonene og alle radseksjonene fordi de alle deler denne verdien.

Deler overskrifter fra radene

Neste trinn er å dele overskriftene fra radene. Jeg legger til en annen egendefinert kolonne og kaller den Temp . Denne gangen skal vi gjøre noe mer forseggjort og utnytte den BufferList som jeg opprettet tidligere.

Vi vil bruke et par listefunksjoner for å se på hver av posisjonene og finne ut om det er samsvar med indeksen.

Jeg starter med en if-setning og bruker List.Contains for å se etter en spesifikk posisjon i BufferList og refererer til spørringen HeaderID .

Vi ønsker å finne den over hele lengden av filen, og deretter returnere posisjonen til elementet i listen. Hvis det samsvarer med indeksen, har vi et samsvar for den spesifikke raden.

Deretter vil jeg returnere en verdi for å identifisere overskriften. I dette tilfellet returnerer jeg bare en H. Jeg kopierer syntaksen slik at jeg ikke trenger å skrive den på nytt.

Vi må også identifisere radseksjonen. Hvis listen ikke inneholder HeaderID , men DetailID , er vi på en rad-seksjon.

Hvis Kolonne1 er en tom tekststreng, vil jeg at den skal forbli tom . Hvis det ikke er tilfelle, vil jeg at det skal være null .

Denne opposisjonen fikk overskriftsraden og returnerte en H, og så fant den en detaljert rad og returnerte en R. Så returnerte den 0-er for alle elementene som er delt innenfor den radseksjonen.

Disse tomme feltene eller nullpunktene er viktige fordi de lar deg fylle ut. Fyll ned vil ikke flytte over de tomme cellene, slik at vi kan eliminere dem senere.

Vi gjør dette i formellinjen og bruker Table.FillDown igjen. Den vil ha en liste med kolonnenavnet, som er Temp- kolonnen vår.

Nå har vi H- og R-verdiene gjentatt over hele denne kolonnen, noe som betyr at vi faktisk kan dele overskriftene fra de detaljerte delene.

Du kan også fylle den ned fra brukergrensesnittet hvis du ikke vil skrive koden. Du kan bare høyreklikke og velge Fyll , deretter Ned .

Fjerning av null- og blanktegn i Microsoft Power Query

Nå som vi har denne retten, kan vi eliminere de tingene vi ikke trenger. Alt som er null eller inneholder en blank er radene som vi ikke trenger og må fjernes. Vi kan eliminere disse ved å filtrere.

Splitting av seksjonene

Når vi har fjernet de tomme og nullpunktene, sitter vi igjen med alt vi trenger. På dette tidspunktet kan vi bare dele seksjonene. Vi kan fokusere på overskriftsradene, og plukke dem ut fordi de har en separat avstand fra alle detaljradene (som også har en separat avstand).

Jeg legger til et nytt trinn i formellinjen som lar meg lage et nytt filter på den samme kolonnen. I dette tilfellet vil jeg bare beholde alle overskriftsseksjonene.

Nå har jeg alle de overskriftsradene her.

Jeg kan velge Kolonne1 , gå til formellinjen, velge Del kolonne , og deretter dele etter posisjoner.

Deretter må strømspørringen selv finne ut av dette. Det vil foreslå et par stillinger. Klikk OK for å godta disse stillingene.

De eneste tingene vi er interessert i fra overskriftene er varen og jobbnummeret .

Inne i formellinjen her kan jeg gi nytt navn til de med Vare og Job # . Dette vil redde meg fra et annet nytt navn på kolonnetrinn.

Etter dette trinnet er alt jeg trenger å gjøre å velge elementet , velg jobbnummeret og selvfølgelig velge overskriftsnøkkelen vår . Da vil jeg fjerne alle de andre kolonnene fordi jeg ikke trenger dem lenger.

Dette blir resultatet. Vi må fortsatt rydde opp i verdiene og fjerne tekstelementet og bindestrekene. Alt vi ønsker er disse verdiene i mellom.

Så vi åpner dette og fjerner markeringen av bindestrekene og elementene.

Nå er alle overskriftene ferdige.

Vi må gjøre den samme prosessen for DetailID også. Jeg må endre navn på disse trinnene slik at det blir lettere for meg å gå tilbake til det litt senere.

Vi går tilbake til den første spørringen vi startet. Vi startet med Filtrerte rader i Applied Steps-ruten.

Jeg skal kopiere dette og legge det til filteret mitt. Denne gangen velger jeg ikke H, men jeg velger R.

Deretter velger jeg Kolonne1, går til Split-kolonnen, delt etter posisjoner, og får deretter kraftspørringen til å finne ut av det.

Dette er hva power-query antyder. La oss prøve dette.

Dette ser faktisk ganske bra ut. Selv de totale radene deler seg perfekt opp. Selvfølgelig er det mange mellomrom fordi vi hadde den innrykket.

Trimming av tekststrengene i Microsoft Power Query

Jeg velger den første kolonnen, og trykker deretter ned + Shift for å velge til kolonne 1.10. Gå til Transform , velg Format , og deretter Trim . Trimming vil bare fjerne overflødige mellomrom foran eller på slutten av strengen, ikke i mellom.

Deretter kan vi bare promotere overskriftene, slik at jeg ikke trenger å skrive inn alle overskriftene eller titlene for disse kolonnene. I deltrinnet ga jeg nytt navn til to kolonner. Nå, selvfølgelig, med 10 kolonner, er det litt av en plage.

Vi må også kvitte oss med disse oververdiene. Fordi vi har totaler, må jeg bruke en av disse tre siste kolonnene fordi de er de eneste radene som har tilleggsverdiene et sted i mellom. Deretter fjerner vi markeringen av disse tomme feltene, bindestrekene og tekstene.

Så fjerner jeg de unødvendige kolonnene, så det gjenstår bare en tabell med bare overskriftene og kun detaljene. Vi trenger en nøkkel for å bringe disse delene sammen igjen.

For det kan vi bruke en selvsammenslåing, slik at vi kan slå sammen tabellen med seg selv for å bringe den informasjonen sammen igjen. På Hjem- fanen velger du Slå sammen og deretter kolonne 5 og samme spørring.

I stedet for AllDetails, vil jeg ha AllHeaders som min første tabell som jeg vil slå sammen med.

Dette har hentet tilbake all informasjon fra overskriftstabellen med en enkelt rad for hvert element og hvert jobbnummer.

Vi brukte en nøkkel for å slå sammen med detaljradene. Hvis jeg trykker av til siden i det hvite rommet her, vil vi se en forhåndsvisning av alle radene som tilhører Header 5.

Vi fjerner den siste kolonnen her, og så er vi ferdige med å fikse den blandede faste kolonnebredden i Microsoft Power-spørringen.

Konklusjon

I denne opplæringen kom vi opp med en måte å løse problemer med blandet fast kolonnebredde ved hjelp av Microsoft Power Query. Hvis du likte innholdet som dekkes i denne spesielle opplæringen, ikke glem å abonnere på LuckyTemplates TV-kanal.

Vi har en enorm mengde innhold som kommer ut hele tiden fra meg selv og en rekke innholdsskapere, som alle er dedikert til å forbedre måten du bruker LuckyTemplates og Power Platform på.

Melissa