Gegevens opmaken in LuckyTemplates: onregelmatige gegevensindelingen aanpakken

In de blogpost van vandaag bespreken we probleem van de week #6 . Ik zal je laten zien hoe je gegevens kunt opmaken in LuckyTemplates met behulp van power query . De onderstaande schermafbeelding is de oplossing die u zou moeten hebben nadat u alle gegevenstransformatie hebt uitgevoerd. Het werkelijke saldo was $ 685.616,33 . Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

We hadden veel deelname aan Challenge #6. Ik waardeer de inspanningen van iedereen om dit voor elkaar te krijgen. Een ding om op te merken is dat we een aantal inzendingen hebben gezien die een klein beetje uit balans waren. Ik zal je tijdens het doornemen van mijn inzending voor deze uitdaging laten zien waarom dat dollarbedrag niet klopte.

Houd er rekening mee dat mijn oplossing voor het formatteren van gegevens in uit meerdere stappen bestond en dat er veel vermeldingen waren die deze stappen samenvatten. Ik zal rekening houden met deze snelkoppelingen voor toekomstige inspanningen die ik heb met dit soort gegevens. De deelnemers hielpen dus niet alleen bij het oplossen van het Probleem van de Week, maar ze hielpen mij ook op de lange termijn bij het ontwikkelen van rapporten.

Ik zal Power Query openen en stap voor stap doornemen hoe ik tot mijn oplossing kwam. Zo ziet het uiteindelijke product eruit.

Maar als ik op de bron klik, zie je dat dit is wat er oorspronkelijk verscheen toen we verbinding maakten met de bestandslocatie waar we het CSV-bestand hadden opgeslagen.

Inhoudsopgave

• Voordelen van opmerkingen
• Redenen voor het verkeerde bedrag
• Kolommen filteren
• De leeftijdskolom maken
• Conclusie

Voordelen van opmerkingen

Bekijk de stappen aan de rechterkant. U kunt de kleine "i" zien die een afkorting is voor informatie. Dit geeft aan dat er opmerkingen zijn gemaakt in de geavanceerde editor.

Laten we hier heel snel de geavanceerde editor openen. Je kunt zien dat de teksten die in het groen staan ​​opmerkingen zijn die me helpen herinneren aan waar ik op dat moment aan dacht.

Het laat een spoor achter van wat ik in het verleden heb gedaan, dus als iemand nieuw binnenkomt om naar het dossier te kijken, kunnen ze in ieder geval een idee hebben van het pad dat ik ben ingeslagen.

We kunnen de opmerkingen aan de zijkant hier zien. De eerste opmerking over Changed Type1 gaat eigenlijk over het veranderen van typen voor de verschillende kolommen. Zoals je kunt zien, is de opmerking die ik heb gemaakt: "Zorg ervoor dat de huidige kolom is ingesteld als decimaal, anders begint het slecht te gaan!"

Dit brengt me op het punt waarom mensen met dat verkeerde saldo komen dat ik je eerder liet zien.

Redenen voor het verkeerde bedrag

Laat me je nog een PBIX-bestand laten zien dat ik heb gekopieerd van iemand die een van die ingangen had. $ 685.615,28 is het verkeerde bedrag en er waren talloze inzendingen met dit resulterende bedrag.

Laten we uitzoeken hoe en waarom dat gebeurde. Als we naar het tabblad Transformeren gaan en op Detect Data Type klikken , dat automatisch alle rijen detecteert, kunnen we zien dat de huidige kolom verschijnt als een geheel getal 64, wat niet is wat we willen zien.

Daarom kloppen de bedragen niet. Er is ook een andere manier waarop deze fout kan verschijnen in uw instellingen. Laten we naar Bestand>Opties en instellingen>Opties gaan.

Onder het GLOBAL-gebied en de sectie Data Load kunnen we Type Detection zien . Ik heb Detecteer kolomtypes en headers gekozen voor ongestructureerde bronnen volgens de instellingen van elk bestand .

Als u de eerste optie selecteert, namelijk Altijd gedetecteerde kolomtypen en koppen voor ongestructureerde bronnen , zou u ook de fout in het saldobedrag hebben gekregen.

Een van onze die een enorme hulp is geweest als het gaat om M-code of power query-taal, is Melissa. Ze liet me een Microsoft-document zien waarin wordt uitgelegd waarom deze fout is opgetreden.

Zoals u kunt zien, heette dit vroeger Projectopties . De optie voor het automatisch detecteren van kolomtypen en koppen voor ongestructureerde bronnen is aangevinkt.

Het document zegt ook dat het alleen gaat op basis van de eerste 200 rijen van uw tabel.

In mijn tabel was het oorspronkelijke aantal rijen ongeveer 4.000 rijen. Dit is dus de reden waarom het niet de juiste bedragen ophaalde.

Laten we teruggaan naar Power Query en beginnen met het formatteren van gegevens in LuckyTemplates. Zoals u kunt zien in de stap Gewijzigd type , is dit een geheel getal 64 met stroom.

Dus wat ik hier heel snel ga doen, is filteren op een van deze factuurnummers om je te laten zien hoe het er voor en na uitziet.

Zodra ik een bepaald factuurnummer selecteer, kunnen we zien dat het huidige bedrag 4.741,01 is.

Zodra ik op dit wijzigingstype klik, ziet u dat de waarde is gewijzigd en afgerond op 4.741.

Dit is dus een snelle en gemakkelijke manier om uit te leggen wat er mis is gegaan met de andere inzendingen voor het Probleem van de Week.

Laten we teruggaan naar mijn oplossing in powerquery M. De volgende stap is waar ik rijen filterde om alleen de lege rijen weer te geven.

De volgende stap is waar ik kolommen heb verwijderd. Ik heb alle kolommen verwijderd, behalve degene die je op de schermafbeeldingen ziet. Merk ook op dat het recordnummer dat oorspronkelijk naar voren kwam, eigenlijk het factuurnummer is.

De volgende stap is het verwijderen van de lege rijen die null waren voor elk veld.

En toen hernoemde ik de eerste kolom die eerder leeg was en veranderde die in Klant ... omdat we nog een veld gaan toevoegen dat mijn eigenlijke klantveld zal zijn.

De volgende stap was om alle waarden van een blanco te vervangen door null in de kolom Klant. De reden waarom we dit moeten doen is vanwege de volgende stap.

Om al deze rijen te vullen, mag geen van deze leeg zijn en moeten ze null zijn.

Om naar beneden te vullen, klikt u op de kolom, gaat u naar Opvullen en vervolgens naar Omlaag .

Zo zie je alle namen in deze kolom naar beneden gaan.

Kolommen filteren

Ik heb hier ook wat meer rijen gefilterd en gefilterd op factuurnummer om de lege rijen te verwijderen.

Ik heb nog een klantkolom toegevoegd waar ik de kolom in de bovenstaande schermafbeelding heb gekopieerd en de nummers voor de naam heb verwijderd.

Ik heb ook de kolommen opnieuw gerangschikt. Ik heb die nieuwe klantenkolom van het allerlaatste naar het allereerste begin verplaatst. En toen verwijderde ik de klantkolom met de punt of de punt aan het einde ervan. Dus nu heb ik nog maar één duidelijke klantkolom zonder de cijfers ernaast.

Vervolgens is de volgende stap Inserted Sum, waarbij ik alle waarden van Current, 1 – 30, 31 – 60, 61 – 90 en 91 tot 120 heb genomen. Ik heb al die kolommen bij elkaar opgeteld om de aangepaste kolom Addition te krijgen .

Een andere manier om dit te doen is door alle kolommen te markeren, naar het tabblad Kolom toevoegen te gaan, op Standaard te klikken en vervolgens op Toevoegen te klikken .

Dit zou u het veld Toevoeging geven zonder iets in Power Query te hoeven typen.

En toen heb ik al die originele kolommen verwijderd: Huidig, 1 – 30, 31 – 60, 61 – 90 en 91 – 120. Daarna heb ik het gewoon omgedoopt tot Balans .

De volgende stap is het toevoegen van een aangepaste kolom. Voor deze uitdaging was het 21 januari toen deze originele gegevens van de klant werden overgenomen.

De datum kwam automatisch naar voren als een tekstfunctie, dus ik veranderde het in een datumfunctie met deze stap.

De leeftijdskolom maken

Om een ​​leeftijdskolom te maken, heb ik de datum van vandaag en de vervaldatum gemarkeerd. Daarna ging ik naar het tabblad Kolom toevoegen en klikte op Dagen aftrekken .

De volgende stap is om dit om te zetten in een geheel getal.

En toen hernoemde ik de kolom van Age naar Days Aged . Daarna heb ik nog twee voorwaardelijke kolommen toegevoegd. Een daarvan is voor de verouderingskolom om deze items opnieuw te categoriseren.

De tweede voorwaardelijke kolom zou helpen bij het sorteren van de verouderingskolom, zodat ze in sequentiële volgorde zouden worden weergegeven.

Conclusie

Dus zo kwam ik met mijn oplossing voor dit probleem van de week. Zodra ik op Sluiten en toepassen heb gedrukt, zijn dit de resultaten.

Ik hoop dat je het leuk vond om de oplossing met mij door te nemen. Als je genoten hebt van deze tutorial over hoe je gegevens kunt formatteren in LuckyTemplates, abonneer je dan op het TV-kanaal voor vergelijkbare inhoud.

Jarrett