[Excel] "Mappen" van values naar variabele rijtoevoeging

[Techprice]

Mijn vraag

Mijn vraag heeft te maken met wat ik noem "variabele rijtoevoeging". Ik wil namelijk dat rijen in een excelblad worden toegevoegd, afhankelijk van de gegevens die in een ander blad komen. Ik leg het even meer in detail uit:

In een bepaalde Form krijg ik data binnen die wordt doorgestuurd naar iets als excel/sheets (in JSON). Alleen is het probleem hier nu mee dat deze data op een andere manier is gestructureerd dan ik wens.
Een sample voorbeeld van hoe de JSON invoer er zou kunnen uitzien:


Context: klanten geven hun naam, e-mail en hun bestelling door. Die bestelling kan bestaan uit maximaal 3 "productklasses" waarvan elke productklasse specifieke eigenschappen kan toegewezen krijgen zoals de hoeveelheid (aangeduid met "aantal") en de verpakkingsmethode (aangeduid met "per").

Nu is dit een heel fijne manier voor klanten om op die manier hun bestelling door te geven, maar: degene die de bestelling moeten verwerken is met deze info maar weinig. Voor de verwerking is het nodig dat de structuur als volgt PER PAKKET is opgelijst:



Context: Het aantal lijnen (rijen) op dit blad moet per klant gelijk zijn aan het totaal aantal pakketten dat werd besteld door die klant. Daarnaast moeten enkele cellen aangepast worden.

Idealiter zou ik dus iets moeten hebben dat per nieuw toegevoegde lijn van de JSON data:
Excel kijkt naar de nieuw toegevoegde cel "Aantal1" en die hoeveelheid lijnen toevoegt in het ander blad onder dezelfde voornaam, achternaam en met de specificaties van "Pakkettype1" en "Per1". Dan moet er gekeken worden naar de cel "Aantal2", vervolgens weer zoveel lijnen toevoegen onder dezelfde naam en met de specificaties van "Pakkettype2" en "Per2". Hetzelfde voor de derde reeks.


Relevante software en hardware die ik gebruik
-Excel/sheets en dergelijke producten

Wat ik al gevonden of geprobeerd heb

Ik ben nu wel geen grote held met Excel, maar ik heb wel al een hele boel dingen geprobeerd. Werken met voorwaardelijke cellen, formules proberen samenstellen, enz.
Ik kom er jammer genoeg echt niet uit. Vooral het feit dat de rijen steeds toegevoegd moeten worden afhankelijk van een nog niet toegevoegde cel maakt het erg moeilijk.

Ik las online al iets over VBA, maar zelf kan ik hier niet mee overweg. Daarnaast vroeg ik me ook af: werkt dat dan nog wel als ik gebruik wil maken als iets van Excel Online of Google Sheets? (Ik vrees van niet...)

Het is namelijk zo dat dit sheet constant geactualiseerd dient te worden vanwege die JSON toevoer.

Daarnaast wil ik ook even melden dat ik volledig open sta om desnoods andere software/programma's te proberen waar dit meer voor geschikt is.

Anyway, dank voor alle reacties bij voorbaat.

[Belindo]

Dus je wilt dat de kolommen voor verschillende producten worden omgezet naar een rij per klant/product combinatie?

Zou dan in je 2e afbeelding de klant 'gsdsg' niet 3 rijen moeten hebben?

[Techprice]

Belindo schreef op donderdag 7 mei 2020 @ 16:10:
Dus je wilt dat de kolommen voor verschillende producten worden omgezet naar een rij per klant/product combinatie?

Zou dan in je 2e afbeelding de klant 'gsdsg' niet 3 rijen moeten hebben?

Ik zou voor die klant in totaal 6 rijen moeten hebben zelfs. Maar ik was het gewoon zat om ze nog verder met de hand over te zetten. Ik stopte dus bij de tweede rij van die klant .

[Belindo]

Oh wacht, omdat aantal 2 is natuurlijk.

Hmm, deel 1 van het probleem kun je in Excel aanpakken door het prachtige vernieuwde Get & Transform (of Power Query). Dan kun je de source data 3x gebruiken, de 1e keer verwijder je product 2 en 3, de 2e keer verwijder je kolom 1 en 3 en de laatste keer verwijder je kolom 1 en 2. Dan wat opschonen en dan de drie tables mergen naar één output table.

Alleen dan is het probleem met de aantallen niet opgelost.

Ik ga hier even mee stoeien

[Belindo]

Volgens mij heb ik iets wat je op weg kunt helpen.

Ik heb je data als volgt overgenomen, en er een tabel van gemaakt:


Vervolgens selecteer je een cel in de tabel en kies je Data > From Table/Range. Dan opent het Power Query scherm.

Allereerst wil je links je Query voor Table 1, nog twee keer kopieren. Want je hebt max 3 pakkettypes.

In de 1e query verwijder je de kolommen voor Pakkettype2, Per2, Aantal2, Pakkettype3, Per3, Aantal3 en de lege 'tussenkolommen'. Vervolgens hernoem je Pakkettype1, Per1 en Aantal1 naar Pakkettype, Per en Aantal.
Vervolgens filter je op Pakkettype en kies je voor 'Remove Empty'. Je houdt dan zoiets over:


Vervolgens doe je je 2e en 3e query op dezelfde manier. Alleen verwijder je in de 2e query de kolommen voor Pakkettype1, Per1, Aantal1, Pakkettype3, Per3, Aantal3 en de lege 'tussenkolommen'.

En in de 3e query verwijder je de kolommen voor Pakkettype1, Per1, Aantal1, Pakkettype2, Per2, Aantal2 en de lege 'tussenkolommen'.

Vervolgens klik je met je rechtermuis in het linker 'Queries' scherm en kies je 'New Query' > 'Append Query' en append je de 3 queries tot een nieuwe. Je krijgt dan het volgende:


Nu is het zaak om de rijen waar het aantal 2 of meer is, te dupliceren. Hiervoor voeg je een Custom Column toe met de volgende formule:

code:

=List.Repeat( { [#"E-mail"] }, [Aantal] )

Je krijgt dan een nieuwe kolom aan de rechterkant met daarin het woord 'List'. Vervolgens klik je bovenaan die nieuwe kolom op het pijltje, en kies je voor 'Expand to new rows'. Nu worden de rijen zo vaak gedupliceerd als het getal in Aantal. Je eindigt dan met dit:


De view die je nu in beeld hebt staan kun je verder aanpassen. Je kunt kolommen verwijderen, hernoemen. En met formules bijvoorbeeld de kolom 'Half?' toevoegen:

code:

=if [Pakkettype] = "half" then "ja" else "nee"

Dan eindig je met zoiets, en kun je het laden in Excel en verder uitwerken:

edit:
Laat me even weten als je het Excel bestand wilt hebben om de queries te zien

[Techprice]

Top man, dat lijkt me inderdaad te voldoen aan wat ik wou! Ik probeer het vanavond direct eens uit.

Anyway, leuk dat Excel zo'n soort van query managment kan doen. Ik wist helemaal niet dat zoiets kon.

Ook heel erg bedankt voor de uitgeschreven, gedetailleerde stappen. Dat moet zelfs mij in staat stellen om dit te kunnen fixen hoop ik .

Excel file doorsturen mag trouwens altijd.
P.s., lukt zoiets ook op een cloud-based sheet? Waar de JSON data steeds geactualiseerd wordt? Of is dat gewoon kwestie van het koppelen van de webhook aan Excel?

[ Voor 23% gewijzigd door Techprice op 07-05-2020 17:19 ]

[Belindo]

Nee, met Excel Online of Google Docs gaat je dit niet lukken. Je kunt als stap 1 natuurlijk altijd je query bouwen op een JSON datasource in plaats van het eerst in Excel zetten zoals ik bij dit voorbeeld heb gedaan. De rest van je stappen blijft dan hetzelfde.

De Power Query kan overweg met legio aan data-connecties

De file: https://easyupload.io/cjhesv
(ik ben een betrouwbare Tweaker, maar doe altijd even een virusscan over bestanden van het internet)

Lijstje van dingen waar je Excel mee kunt verbinden:
a CSV or text file
an Excel table or range
an Excel file
a web page
SQL Server
an ODBC data source
an Access database
an XML file
Analysis Services
a JSON file
Oracle
a SharePoint list
an OData feed
OLE DB
MySQL
Exchange
Active Directory
SAP HANA
IBM DB2
PostgreSQL
Common Data Service for Apps
Teradata
Facebook
SAP Sybase SQL Anywhere
Microsoft Azure Blob Storage
Azure HDInsight

[ Voor 32% gewijzigd door Belindo op 07-05-2020 17:24 ]

[Techprice]

Hmmm, I see. Die Power Query is dus echt wel een sterke tool.

Ik heb er even zitten over nadenken zo nu.

Het probleem is het updaten van die "live-gegevens". Ik kan namelijk enkel de gegevens per entry doorsturen naar een webadres in de vorm van JSON entry.
Ik heb dus ergens een web-based datasysteem nodig dat de entries kan ontvangen. Dit kan ik wel omzeilen via iets als Zapier of Zoho Flow. Die kunnen de JSON gegevens uit dat form simultaan laten omzetten in een sheet dat online staat (in de cloud). Je mapt dan gewoon bepaalde velden aan de juiste kolommen en telkens als er een entry bijkomt maakt Zapier dan een rij aan.

Natuurlijk moet dat bestand dan in de cloud staan en ja, dan werkt die Power Query niet.

Daarnaast gebruiken we geen gebruik van Microsoft services, dus alles als Sharepoint/Azure is sowieso uitgesloten om als databron te gebruiken. Een SQL-server hebben we eveneens niet.

Wat ik wel ga proberen is om een Excel file in de cloud te zetten, wat Zapier dan weer kan gebruiken als doelbestand. En dan dat bestand proberen te laten fungeren als databron voor die Power Query.

Toch bedankt voor alle de nuttige informatie. Ik ga er eens mee aan de slag.

[Techprice]

Bingo! Google sheets ondersteunt het publiceren van het sheet naar een webadres, én in de vorm van een xlsx document. Het lijkt erop dat ik dit kan gebruiken in de Power Query Editor. Ik zie alle kolommen en hopelijk wordt die sheet ook gesynchroniseerd.

[Techprice]

Et voila: getest en goedgekeurd!


En ja ik moet de aantal kolom nog wegdoen lol.

Een remark voor mensen die dit in de toekomst ooit zouden raadplegen: gebruik de "publish as csv" in Google Sheets. Met "publish as xlsx" werkte het niet helemaal lekker. Vooral het updaten kreeg kuren.
Maar met de csv methode en in excel "get data from the web", dan via de gedeelde link uit Google Sheets werkt het helemaal.


Nogmaals dank @Belindo for your precious time. Je bent een held!

[Belindo]

Fijn dat het gelukt is. En leuk dat je dat ook nog even in je topic laat weten Fijne avond!