Microsoft Power Query-zelfstudie over het oplossen van problemen met gemengde vaste kolombreedten

Mudassir: Voor vandaag hebben we een heel interessant probleem om mee te werken. Het probleem met dit bestand is dat het vast is afgebakend door kolommen en ik weet niet hoe ik dit moet oplossen met Microsoft Power Query. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Ten eerste was het voor mij niet eenvoudig om de kolommen dynamisch te verwijderen. Ten tweede hebben we in dit rapport een tabel met een andere kolombreedte en vervolgens een andere tabel met een andere kolombreedte.

Dus als ik één scheidingsteken dynamisch bovenaan toepas, zou ik de gegevens niet netjes kunnen krijgen. Ik wil al deze gegevens uit de tweede tabel halen en mijn productnummers uit de eerste tabel. Ik wil ook het taaknummer in elke rij van alle tabellen.

Ik heb geprobeerd het zelf op te lossen, maar aangezien dit iets te maken heeft met power query had ik hulp nodig van Melissa. Ik dacht dat ze er minstens twee dagen over zou doen, maar ze wist meteen met een oplossing te komen.

Melissa zal ons laten zien hoe ze dit ingewikkelde probleem heeft opgelost. Ik denk dat de meeste mensen met dit soort problemen te maken zullen krijgen en zoeken naar manieren om ze op te lossen.

Melissa: De eerste tip is dat als je naar een bestand met een vaste lengte kijkt, je naar het tabblad Weergave kunt gaan en die Monospaced -optie kunt inschakelen.

We kunnen zien dat het een lettertype met een vaste lengte is. We kunnen ook de kopteksten, begintabellen en subtabellen zien. Dit zijn de onderdelen waarin we geïnteresseerd zijn en waar we uit willen halen.

Zorg er ook voor dat uw formulebalk is ingeschakeld. Het is altijd een goede zaak om het zichtbaar te hebben op uw scherm, aangezien we het vaak zullen gebruiken om kleine wijzigingen aan te brengen in de invoer.

Ik heb een parameter gemaakt voor de bestandslocatie waar ik het CSV-bestand heb opgeslagen. Ik bracht dat binnen als een staging-bestand en plaatste het in mijn bestandslocatieparameter. Vervolgens heb ik een referentie gemaakt en ik zal vanaf die referentie werken. Dus daar kijken we nu naar in Microsoft Power Query.

Inhoudsopgave

• Een indexkolom toevoegen
• Een bufferlijst maken in Microsoft Power Query
• Kopteksten uit de rijen splitsen
• De null- en lege waarden in Microsoft Power Query verwijderen
• De secties splitsen
• De tekststrings inkorten in Microsoft Power Query
• Conclusie

Een indexkolom toevoegen

Wanneer ik aan een bestand als dit begin te werken, moet ik meestal weten wat de eisen van de klant zijn. Ik vraag wat de klant nodig heeft en waar hij op moet letten.

In dit geval willen we het itemnummer en het jobnummer uit de kopteksten, en dan willen we alle details die bij die specifieke kop horen.

We hebben een sleutel nodig om die dingen weer bij elkaar te brengen. Maar als er geen sleutel aanwezig is, dan is mijn doel om een ​​indexkolom toe te voegen . Ik klik op het pictogram van de minitabel, selecteer Indexkolom toevoegen en voeg vervolgens From 0 toe .

Ik zal later een logica gebruiken met lijsten, die de op 0 gebaseerde index hebben. Als uw index vanaf nul begint, betekent dit eigenlijk dat u naar dezelfde rij kunt verwijzen. Anders moet je er 1 van aftrekken om die op 0 gebaseerde positie te krijgen.

Vervolgens moeten we de positie vinden waar onze headers zijn, wat we vrij gemakkelijk kunnen doen omdat die headers constant door het hele bestand worden herhaald.

Laten we om te beginnen deze waarde kopiëren:

Voeg een nieuwe lege query toe, plak die erin en noem dit de HeaderID .

Ik zal hetzelfde proces doen voor de subtabellen. Ik kopieer die tekenreeks, maak nog een lege query en plak die waarde erin. Dit wordt de tekenreeks die we zullen gebruiken bij het zoeken naar gedetailleerde rijen.

Als dit proces op de een of andere manier de koptekst voor een van deze tabellen verandert, hoef ik alleen maar een van de tekenreeksen te wijzigen en het bestand zal weer werken.

Ik hoef niet echt in de M-code te duiken om te zoeken naar die string waarnaar we op zoek zijn. We kunnen dit gewoon als parameter gebruiken.

Laten we de belasting voor deze twee query's inschakelen.

Een bufferlijst maken in Microsoft Power Query

Het eerste dat ik zal doen, is Kolom1 in een lijst veranderen door er een keer naar te verwijzen en het in het geheugen te laden. Op deze manier hoef ik het bestand niet herhaaldelijk aan te roepen.

Ik open de geavanceerde editor en plaats deze helemaal bovenaan. Wanneer u de gebruikersinterface gebruikt om uw code te bouwen, verwijst deze naar de vorige stap.

Wanneer u ergens anders in uw code een bufferstap plaatst en u wilt later een wijziging aanbrengen, zal dit u helpen de wijzigingen aan te brengen in de stap die u handmatig aan het maken bent.

Ik noem dit BufferList en verwijs naar Column1. Om het in het geheugen te laden, voeg ik een stap List.Buffer toe .

Hier is mijn variabele helemaal bovenaan. Ik kan er steeds weer naar verwijzen.

Het eerste dat ik wil bepalen, is waar mijn kopteksten beginnen, omdat ik een sleutel nodig heb om deze koptekstsecties te behouden en een enkele waarde voor al die rijen te krijgen. Om dit te doen, voeg ik een aangepaste kolom toe en noem deze Header .

Ik zal schrijven dat als Column1 gelijk is aan onze Header-ID, ik wil dat mijn indexnummer null is.

Als resultaat vond het de tekst en retourneerde 5 en 23.

Ik heb die waarde nodig voor alle rijen, dus ik moet die invullen. U kunt gewoon met de rechtermuisknop klikken om in te vullen, maar u kunt ook een heel eenvoudige syntaxis gebruiken en die in de formulebalk toevoegen.

In dit geval heb ik Table.FillDown toegevoegd en in de tekststring heb ik aangegeven welke kolom we willen vullen (Header).

Nu hebben we dat ingevuld voor alle rijen. We hebben een sleutel voor alle kopsecties en alle rijsecties omdat ze allemaal deze waarde delen.

Kopteksten uit de rijen splitsen

De volgende stap is het splitsen van de kopteksten van de rijen. Ik zal nog een aangepaste kolom toevoegen en deze Temp noemen . Deze keer gaan we iets uitgebreider doen en gebruikmaken van die BufferList die ik eerder heb gemaakt.

We zullen een aantal lijstfuncties gebruiken om naar elk van de posities te kijken en te zien of er een overeenkomst is met de index.

Ik begin met een if-statement en gebruik List.Contains om naar een specifieke positie in de BufferList te zoeken en naar de query HeaderID te verwijzen .

We willen het over de hele lengte van het bestand vinden en vervolgens de positie van het item in de lijst retourneren. Als het overeenkomt met de index, hebben we een overeenkomst voor die specifieke rij.

Vervolgens wil ik een waarde retourneren om de koptekst te identificeren. In dit geval geef ik gewoon een H terug. Ik kopieer de syntaxis, zodat ik het niet helemaal opnieuw hoef te schrijven.

We moeten ook de rijsectie identificeren. Als de lijst niet de HeaderID , maar de DetailID bevat , dan zitten we op een rijsectie.

Als Column1 een lege tekenreeks is, wil ik dat deze leeg blijft . Als dat niet het geval is, dan wil ik dat het null is .

Deze oppositie kreeg de koprij en retourneerde een H, en vond vervolgens een gedetailleerde rij en retourneerde een R. Vervolgens retourneerde het nullen voor alle items die binnen die rijsectie worden gedeeld.

Deze blanco's of nullen zijn belangrijk omdat ze u in staat stellen om in te vullen. Fill-down beweegt niet over die lege cellen, dus die kunnen we later verwijderen.

We doen dit in de formulebalk en gebruiken opnieuw Table.FillDown . Het wil een lijst met de kolomnaam, wat onze Temp- kolom is.

Nu hebben we de H- en R-waarden overal in deze kolom herhaald, wat betekent dat we de koppen daadwerkelijk kunnen splitsen van de gedetailleerde secties.

U kunt het ook invullen vanuit de gebruikersinterface als u de code niet wilt schrijven. U kunt gewoon met de rechtermuisknop klikken en Vullen en vervolgens Omlaag selecteren .

De null- en lege waarden in Microsoft Power Query verwijderen

Nu we dit recht hebben, kunnen we de dingen elimineren die we niet nodig hebben. Alles dat null is of een lege ruimte bevat, zijn de rijen die we niet nodig hebben en moeten worden verwijderd. Die kunnen we elimineren door te filteren.

De secties splitsen

Zodra we die spaties en nullen hebben verwijderd, hebben we alles over wat we nodig hebben. Op dit punt kunnen we de secties gewoon splitsen. We kunnen ons concentreren op de koprijen en ze eruit halen omdat ze een aparte afstand hebben ten opzichte van alle detailrijen (die ook een aparte afstand hebben).

Ik voeg een nieuwe stap toe aan de formulebalk waarmee ik een ander filter voor diezelfde kolom kan maken. In dit geval bewaar ik gewoon alle koptekstsecties.

Nu heb ik al die koprijen hier.

Ik kan Kolom1 selecteren , naar de formulebalk gaan, Kolom splitsen selecteren en vervolgens splitsen op posities.

Laat Power Query dit dan zelf uitzoeken. Het zal een aantal posities voorstellen. Klik op OK om die posities te accepteren.

Het enige waar we in de headers in geïnteresseerd zijn, is het item en het jobnummer .

In de formulebalk hier kan ik die hernoemen met Item en Job # . Dit bespaart me een nieuwe kolomnaamstap.

Na deze stap hoef ik alleen maar het item te selecteren , het taaknummer te selecteren en natuurlijk onze koptekstsleutel te selecteren . Dan verwijder ik alle andere kolommen want die heb ik niet meer nodig.

Dit zal het resultaat zijn. We moeten nog steeds de waarden opschonen en het tekstitem en de streepjes verwijderen. Het enige wat we willen zijn die waarden daar tussenin.

Dus we openen dit en maken de selectie van de streepjes en items ongedaan.

Nu zijn alle headers klaar.

We moeten hetzelfde proces ook voor DetailID doen . Ik moet die stappen een andere naam geven, zodat ik er later gemakkelijker op terug kan komen.

We gaan terug naar de oorspronkelijke zoekopdracht die we hebben gestart. We zijn begonnen met gefilterde rijen in het deelvenster Toegepaste stappen.

Ik ga dit kopiëren en toevoegen aan mijn filter. Deze keer selecteer ik niet H, maar ik selecteer de R.

Vervolgens selecteer ik Kolom1, ga naar de kolom Splitsen, splitsen op posities en laat Power Query het uitzoeken.

Dit is wat powerquery suggereert. Laten we dit eens proberen.

Dit ziet er eigenlijk best goed uit. Zelfs de totale rijen splitsen zich perfect op. Natuurlijk zijn er veel spaties omdat we die inspringing hadden.

De tekststrings inkorten in Microsoft Power Query

Ik selecteer die eerste kolom en druk vervolgens op Onder+Shift om te selecteren tot Kolom 1.10. Ga naar Transformeren , selecteer Formaat en dan Trimmen . Trimmen verwijdert alleen de overtollige spaties voor of aan het einde van de string, niet ertussen.

Vervolgens kunnen we alleen de koppen promoveren, zodat ik niet alle koppen of titels voor deze kolommen hoef te typen. In de gesplitste stap heb ik twee kolommen hernoemd. Nu is dat met 10 kolommen natuurlijk een beetje lastig.

Ook van die overwaardes moeten we af. Omdat we totalen hebben, moet ik een van deze laatste drie kolommen gebruiken, omdat dit de enige rijen zijn met de aanvullende waarden ergens tussenin. Vervolgens deselecteren we die spaties, streepjes en teksten.

Daarna verwijder ik de onnodige kolommen zodat er alleen een tabel overblijft met alleen de koppen en alleen de details. We hebben een sleutel nodig om die secties weer bij elkaar te brengen.

Daarvoor kunnen we een zelf-samenvoeging gebruiken, zodat we de tabel met zichzelf kunnen samenvoegen om die informatie weer bij elkaar te brengen. Selecteer op het tabblad Start de optie Samenvoegen en selecteer vervolgens Kolom 5 en dezelfde query.

In plaats van AllDetails wil ik AllHeaders als mijn eerste tabel waarmee ik wil samenvoegen.

Hierdoor is alle informatie uit de kopteksttabel teruggebracht met een enkele rij voor elk item en elk taaknummer.

We hebben een sleutel gebruikt om samen te voegen met de detailrijen. Als ik hier opzij in de witte ruimte druk, zien we een voorbeeld van alle rijen die bij Kop 5 horen.

We zullen hier de laatste kolom verwijderen en dan zijn we klaar met het corrigeren van de gemengde vaste kolombreedte in Microsoft Power Query.

Conclusie

In deze zelfstudie hebben we een manier bedacht om problemen met gemengde vaste kolombreedten op te lossen met behulp van Microsoft Power Query. Als je genoten hebt van de inhoud die in deze specifieke tutorial wordt behandeld, vergeet dan niet je te abonneren op het LuckyTemplates TV-kanaal.

Er komt voortdurend een enorme hoeveelheid content uit van mijzelf en een reeks contentmakers, die allemaal toegewijd zijn aan het verbeteren van de manier waarop u LuckyTemplates en het Power Platform gebruikt.

Melissa