Microsoft Power Query Selvstudium om, hvordan du løser problemer med blandede faste kolonnebredder

Mudassir: For i dag har vi et meget interessant problem at arbejde med. Problemet med denne fil er, at den er løst afgrænset af kolonner, og jeg ved ikke, hvordan jeg løser dette ved hjælp af Microsoft Power Query. Du kan se den fulde video af denne tutorial nederst på denne blog.

For det første var det ikke nemt for mig at slette kolonnerne dynamisk. For det andet har vi i denne rapport en tabel med en anden kolonnebredde og derefter en anden tabel med en anden kolonnebredde.

Så hvis jeg anvender én afgrænser dynamisk på toppen, ville jeg ikke være i stand til at få dataene pænt. Jeg ønsker at få alle disse data fra den anden tabel, og mine produktnumre fra den første tabel. Jeg vil også have jobnummeret i hver eneste række af alle tabellerne.

Jeg prøvede at løse det på egen hånd, men da dette har noget at gøre med strømforespørgsel, havde jeg brug for hjælp fra Melissa. Jeg troede, at det ville tage hende mindst to dage, men det lykkedes hende at finde en løsning med det samme.

Melissa vil vise os, hvordan hun løste dette komplicerede problem. Jeg tror, ​​de fleste mennesker vil beskæftige sig med denne slags problemer og leder efter måder, hvordan de kan løse dem.

Melissa: Det første tip er, at hvis du kigger på en fil med fast længde, kan du gå til fanen Vis og slå den Monospaced- indstilling til.

Vi kan se, at det er en skrifttype med fast længde. Vi kan også se overskrifter, indledende tabeller og undertabeller. Det er de dele, vi er interesserede i og ønsker at udvinde fra.

Sørg også for at have din formellinje aktiveret. Det er altid en god ting at have det synligt på din skærm, da vi vil bruge det ofte til at foretage små ændringer i inputtet.

Jeg har oprettet en parameter for den filplacering, hvor jeg har gemt CSV-filen. Jeg bragte den ind som en iscenesættelsesfil og indsatte den i min filplaceringsparameter. Så oprettede jeg en reference, og jeg arbejder ud fra den reference. Så det er det, vi kigger på lige nu i Microsoft Power-forespørgsel.

Indholdsfortegnelse

• Tilføjelse af en indekskolonne
• Oprettelse af en bufferliste i Microsoft Power Query
• Opdeling af overskrifter fra rækkerne
• Fjernelse af nul- og blanktegn i Microsoft Power Query
• Opdeling af sektionerne
• Trimning af tekststrengene i Microsoft Power Query
• Konklusion

Tilføjelse af en indekskolonne

Normalt, når jeg begynder at arbejde på en fil som denne, skal jeg kende kundens krav. Jeg spørger, hvad kunden har brug for, og hvad man skal kigge efter.

I dette tilfælde vil vi have varenummeret og jobnummeret fra overskrifterne, og så vil vi have alle de detaljer, der hører til den specifikke overskrift.

Vi skal bruge en nøgle til at bringe de ting sammen igen. Men hvis der ikke er nogen nøgle til stede, så er min go-to at tilføje en . Jeg klikker på minitabelikonet, vælger Tilføj indekskolonne og tilføjer derefter Fra 0 .

Jeg vil bruge en logik senere med lister, som har det 0-baserede indeks. At lade dit indeks starte fra nul betyder faktisk, at du kan referere til den samme række. Ellers bliver du nødt til at trække 1 fra for at komme til den 0-baserede position.

Derefter skal vi finde den position, hvor vores overskrifter er, hvilket vi kan gøre ret nemt, fordi disse overskrifter gentages konstant gennem hele filen.

Lad os for at starte med at kopiere denne værdi:

Tilføj en ny tom forespørgsel, indsæt den, og kald dette HeaderID .

Jeg vil gøre den samme proces for undertabellerne. Jeg kopierer den tekststreng, opretter endnu en tom forespørgsel og indsætter den værdi i. Dette bliver den streng, vi vil bruge, når vi leder efter detaljerede rækker.

Hvis denne proces på en eller anden måde ændrer overskriften for nogen af ​​disse tabeller, er alt, hvad jeg skal gøre, at ændre en af ​​tekststrengene, og filen vil fungere igen.

Jeg behøver ikke rigtig at dykke ned i M-koden for at søge efter den streng, vi leder efter. Vi kan bare bruge dette som en parameter.

Lad os aktivere belastningen for disse to forespørgsler.

Oprettelse af en bufferliste i Microsoft Power Query

Den første ting, jeg vil gøre, er at gøre Kolonne1 til en liste ved at referere og indlæse den i hukommelsen én gang. På denne måde behøver jeg ikke foretage gentagne opkald til filen.

Jeg åbner den avancerede editor og placerer den helt øverst. Når du bruger brugergrænsefladen til at bygge din kode, refererer den til det forrige trin.

Når du placerer et buffertrin et andet sted i din kode, og du vil lave en ændring, vil det senere hjælpe dig med at foretage ændringerne til det trin, du manuelt opretter.

Jeg kalder denne BufferList og refererer til Column1. For at indlæse det i hukommelsen, tilføjer jeg et List.Buffer- trin.

Her er min variabel helt øverst. Jeg kan referere til det igen og igen.

Den første ting, jeg vil bestemme, er, hvor mine overskrifter starter, fordi jeg har brug for en nøgle til at beholde disse overskriftssektioner og få en enkelt værdi for alle disse rækker. For at gøre dette vil jeg tilføje en brugerdefineret kolonne og kalde den Header .

Jeg vil skrive, at hvis Column1 er lig med vores Header ID, så vil jeg have mit indeksnummer til at være null.

Som et resultat fandt den teksten og returnerede 5 og 23.

Jeg har brug for den værdi på tværs af alle rækkerne, så jeg skal udfylde den. Du kan bare højreklikke for at udfylde, men du kan også bruge meget simpel syntaks og tilføje det i formellinjen.

I dette tilfælde tilføjede jeg Table.FillDown og i tekststrengen angav jeg hvilken kolonne vi vil udfylde (Header).

Nu har vi udfyldt det for alle rækkerne. Vi har en nøgle til alle overskriftssektioner og alle rækkesektioner, fordi de alle deler denne værdi.

Opdeling af overskrifter fra rækkerne

Det næste trin er at opdele overskrifterne fra rækkerne. Jeg tilføjer endnu en brugerdefineret kolonne og kalder den Temp . Denne gang vil vi gøre noget mere omfattende og udnytte den BufferList , som jeg oprettede tidligere.

Vi vil bruge et par listefunktioner til at se på hver af positionerne og finde ud af, om der er et match til indekset.

Jeg starter med en if-sætning og bruger List.Contains til at lede efter en specifik position i BufferList og henvise til forespørgslen HeaderID .

Vi ønsker at finde det i hele filens længde og derefter returnere elementets position på listen. Hvis det matcher indekset, har vi et match for den specifikke række.

Så vil jeg returnere en værdi for at identificere overskriften. I dette tilfælde returnerer jeg bare et H. Jeg kopierer syntaksen, så jeg ikke behøver at skrive det hele igen.

Vi skal også identificere rækkesektionen. Hvis listen ikke indeholder HeaderID , men DetailID , så er vi på en række sektion.

Hvis Kolonne1 er en tom tekststreng, vil jeg gerne have, at den forbliver tom . Hvis det ikke er tilfældet, så vil jeg gerne have, at den er nul .

Denne opposition fik overskriftsrækken og returnerede et H, og så fandt den en detaljeret række og returnerede et R. Derefter returnerede den 0'er for alle de elementer, der er delt i den rækkesektion.

Disse tomme felter eller nuller er vigtige, fordi de giver dig mulighed for at udfylde. Fyld ned vil ikke flytte hen over de tomme celler, så vi kan fjerne dem senere.

Vi gør dette i formellinjen og bruger Table.FillDown igen. Den vil have en liste med kolonnenavnet, som er vores Temp- kolonne.

Nu har vi H- og R-værdierne gentaget over hele denne kolonne, hvilket betyder, at vi faktisk kan opdele overskrifterne fra de detaljerede sektioner.

Du kan også udfylde den fra brugergrænsefladen, hvis du ikke vil skrive koden. Du kan bare højreklikke og vælge Fyld og derefter Ned .

Fjernelse af nul- og blanktegn i Microsoft Power Query

Nu hvor vi har denne ret, kan vi fjerne de ting, vi ikke har brug for. Alt, der er null eller indeholder en tom, er de rækker, som vi ikke har brug for og skal fjernes. Vi kan fjerne dem ved at filtrere.

Opdeling af sektionerne

Når vi har fjernet de tomme felter og nuller, står vi tilbage med alt, hvad vi har brug for. På dette tidspunkt kan vi bare opdele sektionerne. Vi kan fokusere på overskriftsrækkerne og vælge dem ud, fordi de har et separat mellemrum fra alle detaljerækkerne (som også har et separat mellemrum).

Jeg tilføjer et nyt trin i formellinjen, som giver mig mulighed for at oprette et andet filter på den samme kolonne. I dette tilfælde vil jeg bare beholde alle overskriftssektionerne.

Nu har jeg alle de overskriftsrækker her.

Jeg kan vælge Kolonne1 , gå til formellinjen, vælge Opdel kolonne og derefter opdele efter positioner.

Lad derefter strømforespørgslen selv finde ud af denne. Det vil foreslå et par positioner. Klik på OK for at acceptere disse positioner.

De eneste ting, vi er interesserede i fra overskrifterne, er varen og jobnummeret .

Inde i formellinjen her kan jeg omdøbe dem med Vare og Job # . Dette vil redde mig fra et andet omdøbningskolonnetrin.

Efter dette trin er alt, hvad jeg skal gøre, at vælge elementet , vælge jobnummeret og selvfølgelig vælge vores hovednøgle . Så fjerner jeg alle de andre kolonner, fordi jeg ikke længere har brug for dem.

Dette bliver resultatet. Vi mangler stadig at rydde op i værdierne og fjerne tekstelementet og bindestreger. Det eneste, vi ønsker, er disse værdier imellem.

Så vi åbner dette og fjerner markeringen af ​​bindestreger og elementer.

Nu er alle overskrifter færdige.

Vi skal også gøre den samme proces for DetailID . Jeg bliver nødt til at omdøbe disse trin, så det bliver nemmere for mig at gå tilbage til det lidt senere.

Vi går tilbage til den oprindelige forespørgsel, vi startede. Vi startede med Filtrerede rækker i ruden Anvendte trin.

Jeg vil kopiere dette og tilføje det til mit filter. Denne gang vælger jeg ikke H, men jeg vælger R.

Så vælger jeg Kolonne1, går til Split-kolonnen, opdeler efter positioner, og får derefter power-forespørgslen til at finde ud af det.

Dette er, hvad power-forespørgsel antyder. Lad os prøve dette.

Det her ser faktisk ret godt ud. Selv de samlede rækker opdeles perfekt. Selvfølgelig er der mange mellemrum, fordi vi havde den fordybning.

Trimning af tekststrengene i Microsoft Power Query

Jeg vælger den første kolonne, og tryk derefter på Ned + Skift for at vælge indtil kolonne 1.10. Gå til Transform , vælg Formater og derefter Trim . Trimning vil kun fjerne de overskydende mellemrum foran eller for enden af ​​strengen, ikke midt imellem.

Dernæst kan vi bare promovere overskrifterne, så jeg ikke behøver at skrive alle overskrifterne eller titlerne til disse kolonner. I opdelingstrinnet omdøbte jeg to kolonner. Nu, selvfølgelig, med 10 kolonner, er det lidt af en plage.

Vi skal også af med de overskydende værdier. Fordi vi har totaler, er jeg nødt til at bruge en af ​​disse sidste tre kolonner, fordi de er de eneste rækker, der har de ekstra værdier et sted midt imellem. Derefter fravælger vi disse tomme felter, bindestreger og tekster.

Så fjerner jeg de unødvendige kolonner, så der kun er en tabel tilbage med kun overskrifterne og kun detaljerne. Vi har brug for en nøgle til at bringe de sektioner sammen igen.

Til det kan vi bruge en selvfletning, så vi kan flette tabellen med sig selv for at bringe disse oplysninger sammen igen. På fanen Hjem skal du vælge Flet , derefter vælge kolonne 5 og den samme forespørgsel.

I stedet for AllDetails vil jeg have AllHeaders som min indledende tabel, som jeg vil fusionere med.

Dette har bragt alle oplysningerne tilbage fra overskriftstabellen med en enkelt række for hver vare og hvert jobnummer.

Vi brugte en nøgle til at flette med detaljerækkerne. Hvis jeg trykker ud til siden i det hvide rum her, vil vi se en forhåndsvisning af alle de rækker, der hører til Header 5.

Vi fjerner den sidste kolonne her, og så er vi færdige med at rette den blandede faste kolonnebredde i Microsoft Power-forespørgsel.

Konklusion

I dette selvstudium fandt vi på en måde at løse blandede problemer med fast kolonnebredde ved hjælp af Microsoft Power Query. Hvis du kunne lide indholdet i denne særlige vejledning, så glem ikke at abonnere på LuckyTemplates tv-kanal.

Vi har en enorm mængde indhold, der kommer ud hele tiden fra mig selv og en række indholdsskabere, som alle er dedikerede til at forbedre den måde, du bruger LuckyTemplates og Power Platform på.

Melissa