Handledning för Microsoft Power Query om hur man åtgärdar blandade problem med fast kolumnbredd

Mudassir: För idag har vi ett mycket intressant problem att arbeta med. Problemet med den här filen är att den är fixad avgränsad av kolumner och jag vet inte hur jag ska lösa detta med Microsoft Power Query. Du kan se hela videon av denna handledning längst ner på den här bloggen.

För det första var det inte lätt för mig att radera kolumnerna dynamiskt. För det andra, i den här rapporten har vi en tabell med en annan kolumnbredd och sedan en annan tabell med en annan kolumnbredd.

Så om jag använder en avgränsare dynamiskt på toppen, skulle jag inte kunna få informationen snyggt. Jag vill få all denna data från den andra tabellen och mina produktnummer från den första tabellen. Jag vill också ha jobbnumret i varje rad i alla tabeller.

Jag försökte lösa det på egen hand, men eftersom detta har något att göra med strömförfrågan behövde jag hjälp från Melissa. Jag trodde att det skulle ta henne minst två dagar, men hon lyckades komma på en lösning direkt.

Melissa kommer att visa oss hur hon löste detta komplicerade problem. Jag tror att de flesta kommer att ta itu med den här typen av problem och letar efter sätt att lösa dem.

Melissa: Det första tipset är att om du tittar på en fil med fast längd kan du gå till fliken Visa och aktivera alternativet Monospace .

Vi kan se att det är ett typsnitt med fast längd. Vi kan också se rubriker, initiala tabeller och deltabeller. Det är dessa delar som vi är intresserade av och vill ta ut ur.

Se också till att ha din formelfält aktiverad. Det är alltid bra att ha det synligt på din skärm eftersom vi kommer att använda det ofta för att göra små ändringar i inmatningen.

Jag har skapat en parameter för filplatsen där jag har lagrat CSV-filen. Jag tog in den som en mellanlagringsfil och skickade in den i min filplatsparameter. Sedan skapade jag en referens och jag kommer att arbeta från den referensen. Så det är vad vi tittar på just nu i Microsoft Power Query.

Innehållsförteckning

• Lägga till en indexkolumn
• Skapa en buffertlista i Microsoft Power Query
• Dela rubriker från raderna
• Ta bort noll- och blanktecken i Microsoft Power Query
• Dela upp sektionerna
• Trimma textsträngarna i Microsoft Power Query
• Slutsats

Lägga till en indexkolumn

Vanligtvis, när jag börjar arbeta med en fil som denna, måste jag känna till kundens krav. Jag frågar vad kunden behöver och vad man ska leta efter.

I det här fallet vill vi ha artikelnumret och jobbnumret från rubrikerna, och sedan vill vi ha alla detaljer som hör till den specifika rubriken.

Vi behöver en nyckel för att få ihop dessa saker igen. Men om det inte finns någon nyckel, då är mitt mål att lägga till en . Jag klickar på minitabellikonen, väljer Lägg till indexkolumn och lägger sedan till Från 0 .

Jag kommer att använda en logik senare med listor som har det 0-baserade indexet. Att låta ditt index börja från noll betyder faktiskt att du kan referera till samma rad. Annars måste du subtrahera 1 för att komma till den 0-baserade positionen.

Sedan måste vi hitta positionen där våra rubriker är, vilket vi kan göra ganska enkelt eftersom dessa rubriker upprepas hela tiden genom hela filen.

Till att börja med, låt oss kopiera detta värde:

Lägg till en ny tom fråga, klistra in den och kalla detta HeaderID .

Jag kommer att göra samma process för deltabellerna. Jag kopierar den textsträngen, skapar en annan tom fråga och klistra in det värdet i. Det här kommer att vara strängen vi kommer att använda när vi letar efter detaljerade rader.

Om den här processen på något sätt ändrar rubriken för någon av dessa tabeller, är allt jag behöver göra att ändra en av textsträngarna och filen kommer att fungera igen.

Jag behöver verkligen inte dyka in i M-koden för att söka efter den strängen som vi letar efter. Vi kan bara använda detta som en parameter.

Låt oss aktivera belastningen för dessa två frågor.

Skapa en buffertlista i Microsoft Power Query

Det första jag ska göra är att förvandla kolumn1 till en lista genom att referera och ladda den i minnet en gång. På så sätt behöver jag inte göra upprepade anrop till filen.

Jag öppnar den avancerade redigeraren och placerar den hela vägen överst. När du använder användargränssnittet för att bygga din kod kommer det att referera till föregående steg.

När du placerar ett buffertsteg någon annanstans i din kod och du vill göra en modifiering, kommer det senare att hjälpa dig att göra ändringarna i steget som du skapar manuellt.

Jag kallar denna BufferList och refererar till kolumn1. För att ladda den i minnet lägger jag till ett List.Buffer- steg.

Här är min variabel hela vägen överst. Jag kan referera till det om och om igen.

Det första jag vill bestämma är var mina rubriker börjar eftersom jag behöver en nyckel för att behålla dessa rubriksektioner och få ett enda värde för alla dessa rader. För att göra detta lägger jag till en anpassad kolumn och kallar den Header .

Jag skriver att om Kolumn1 är lika med vårt Header ID, så vill jag att mitt indexnummer ska vara null.

Som ett resultat hittade den texten och returnerade 5 och 23.

Jag behöver det värdet över alla rader, så jag måste fylla i det. Du kan bara högerklicka för att fylla i, men du kan också använda väldigt enkel syntax och lägga till det i formelfältet.

I det här fallet lade jag till Table.FillDown och i textsträngen angav jag vilken kolumn vi vill fylla ner (Header).

Nu har vi fyllt i det för alla rader. Vi har en nyckel för alla rubriksektioner och alla radsektioner eftersom de alla delar detta värde.

Dela rubriker från raderna

Nästa steg är att dela upp rubrikerna från raderna. Jag lägger till en annan anpassad kolumn och kallar den Temp . Den här gången ska vi göra något mer utarbetat och dra nytta av den BufferList som jag skapade tidigare.

Vi kommer att använda ett par listfunktioner för att titta på var och en av positionerna och se om det finns en matchning till indexet.

Jag börjar med en if-sats och använder List.Contains för att leta efter en specifik position i BufferList och referera till frågan HeaderID .

Vi vill hitta den över hela filens längd och sedan returnera objektets position i listan. Om det matchar indexet har vi en matchning för den specifika raden.

Sedan vill jag returnera ett värde för att identifiera rubriken. I det här fallet returnerar jag bara ett H. Jag kopierar syntaxen så att jag inte behöver skriva om allt igen.

Vi måste också identifiera radsektionen. Om listan inte innehåller HeaderID , utan DetailID , är vi på en radsektion.

Om kolumn1 är en tom textsträng vill jag att den ska förbli tom . Om så inte är fallet vill jag att det ska vara null .

Den här oppositionen fick rubrikraden och returnerade ett H, och sedan hittade den en detaljerad rad och returnerade ett R. Sedan returnerade den 0:or för alla objekt som delas inom den radsektionen.

Dessa tomrum eller nollor är viktiga eftersom de låter dig fylla i. Fyll ner kommer inte att flytta över de tomma cellerna så vi kan eliminera dem senare.

Vi gör detta i formelfältet och använder Table.FillDown igen. Den vill ha en lista med kolumnnamnet, vilket är vår Temp- kolumn.

Nu har vi H- och R-värdena upprepade över hela den här kolumnen, vilket betyder att vi faktiskt kan dela upp rubrikerna från de detaljerade avsnitten.

Du kan också fylla i det från användargränssnittet om du inte vill skriva koden. Du kan bara högerklicka och välja Fyll , sedan Ner .

Ta bort noll- och blanktecken i Microsoft Power Query

Nu när vi har denna rätt kan vi eliminera de saker som vi inte behöver. Allt som är null eller innehåller ett tomt är de rader som vi inte behöver och måste tas bort. Vi kan eliminera dem genom att filtrera.

Dela upp sektionerna

När vi har tagit bort dessa tomrum och nollor, har vi allt vi behöver. Vid det här laget kan vi bara dela upp sektionerna. Vi kan fokusera på rubrikraderna och välja ut dem eftersom de har ett separat avstånd från alla detaljrader (som också har ett separat avstånd).

Jag lägger till ett nytt steg i formelfältet som låter mig skapa ett annat filter på samma kolumn. I det här fallet kommer jag bara att behålla alla rubriker.

Nu har jag alla dessa rubrikrader här.

Jag kan välja Kolumn1 , gå till formelfältet, välj Dela kolumn och sedan dela upp efter positioner.

Låt sedan maktfrågan själv räkna ut detta. Det kommer att föreslå ett par positioner. Klicka på OK för att acceptera dessa positioner.

Det enda vi är intresserade av från rubrikerna är artikeln och jobbnumret .

Inne i formelfältet här kan jag byta namn på dem med objekt och jobb # . Detta kommer att rädda mig från ett annat döpa kolumnsteg.

Efter det här steget behöver jag bara välja objektet , välja jobbnummer och naturligtvis välja vår huvudnyckel . Sedan tar jag bort alla andra kolumner eftersom jag inte längre behöver dem.

Detta blir resultatet. Vi behöver fortfarande rensa upp värdena och ta bort textobjektet och bindestreck. Allt vi vill ha är dessa värderingar däremellan.

Så vi öppnar det här och avmarkerar bindestreck och objekt.

Nu är alla rubriker klara.

Vi måste göra samma process för DetailID också. Jag måste byta namn på dessa steg så att det blir lättare för mig att gå tillbaka till det lite senare.

Vi går tillbaka till den första frågan som vi startade. Vi började med Filtrerade rader i rutan Tillämpade steg.

Jag ska kopiera detta och lägga till det i mitt filter. Den här gången väljer jag inte H utan jag väljer R.

Sedan väljer jag Kolumn1, går till Split-kolumnen, delas upp efter positioner, och får sedan maktfrågan att räkna ut det.

Detta är vad power-frågan föreslår. Låt oss ge detta ett försök.

Det här ser faktiskt ganska bra ut. Även de totala raderna delas upp perfekt. Naturligtvis finns det många utrymmen eftersom vi hade den indragningen.

Trimma textsträngarna i Microsoft Power Query

Jag väljer den första kolumnen och trycker sedan ner + Skift för att välja tills kolumn 1.10. Gå till Transform , välj Format och sedan Trim . Trimning tar bara bort överflödiga utrymmen framför eller i slutet av strängen, inte däremellan.

Därefter kan vi bara marknadsföra rubrikerna, så jag behöver inte skriva in alla rubriker eller rubriker för dessa kolumner. I det delade steget döpte jag om två kolumner. Nu, naturligtvis, med 10 kolumner, det är lite av ett besvär.

Vi måste också bli av med de övervärdena. Eftersom vi har totaler måste jag använda en av dessa tre sista kolumner eftersom de är de enda raderna som har ytterligare värden någonstans däremellan. Sedan avmarkerar vi dessa blanksteg, bindestreck och texter.

Sedan tar jag bort de onödiga kolumnerna så att allt som återstår är en tabell med bara rubrikerna och bara detaljerna. Vi behöver en nyckel för att få ihop dessa sektioner igen.

För det kan vi använda en självsammanfogning så att vi kan slå samman tabellen med sig själv för att sammanföra den informationen igen. På fliken Hem väljer du Sammanfoga och sedan kolumn 5 och samma fråga.

Istället för AllDetails vill jag ha AllHeaders som min första tabell som jag vill slå samman med.

Detta har tagit tillbaka all information från rubriktabellen med en enda rad för varje artikel och varje jobbnummer.

Vi använde en nyckel för att slå samman med detaljraderna. Om jag trycker åt sidan i det vita utrymmet här ser vi en förhandsvisning av alla rader som hör till Header 5.

Vi tar bort den sista kolumnen här och sedan är vi klara med att fixa den blandade fasta kolumnbredden i Microsoft Power-frågan.

Slutsats

I den här handledningen kom vi på ett sätt att lösa blandade problem med fast kolumnbredd med hjälp av Microsoft Power Query. Om du gillade innehållet i denna handledning, glöm inte att prenumerera på LuckyTemplates TV-kanal.

Vi har en enorm mängd innehåll som kommer ut hela tiden från mig själv och en rad innehållsskapare, som alla är dedikerade till att förbättra ditt sätt att använda LuckyTemplates och Power Platform.

Melissa