Power Query te veel rijen en te weinig kolommen

[PeteyB]

beste lezer,

ik stuit tegen het volgende probleem en heel eerlijk begrijp ik de taal van power query niet helemaal. ik ben de draad compleet kwijt als ik dingen moet groeperen, tabellen maken en dan weer de oude kolommen moet terug halen. mogelijk kunnen jullie mij hierbij helpen.

Om mijn probleem te verduidelijken heb ik een bestandje gemaakt met 2 tabbladen. 1 tabblad met gegevens hoe ik het krijg aangeleverd. en 1 tabblad met gegevens hoe ik dit graag in het vervolg kan krijgen via PowerQuery. De reden dat ik dit in PowerQuery wil hebben is omdat ik de documentatie krijg aangeleverd in JSON, maar voor mij de data nog niet "plat" genoeg is.

Hopelijk kunnen jullie mij hierbij helpen. bij voorbaat heel heel veel dank.

 

[AHulpje]

Probeer het hier eens mee:

 

[JEC.]

Het is nog vrij simpel te doen in Power Query
Dit in de advanced editor

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    grp = Table.Group(Source, {"Adres","huisnr"}, {{"Count", each Table.PromoteHeaders(Table.Transpose(Table.UnpivotOtherColumns(_, {}, "Attribute", "Value")))}}),
    out= Table.Combine(grp[Count])
in
    out

 

[JEC.]

Als je de eerste twee kolommen niet wilt laten terugkeren bij de andere namen. (Adres en huisnr)

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    grp = Table.Group(Source, {"Adres","huisnr"}, {{"Count", each 
      let 
         tbl = Table.PromoteHeaders(Table.Transpose(Table.UnpivotOtherColumns(_, {}, "Attribute", "Value"))) 
      in 
         Table.SelectColumns(tbl, List.Select(Table.ColumnNames(tbl),(x)=> not Text.Contains(x,"Adres_") and not Text.Contains(x,"huisnr_"))) }}),
    out= Table.Combine(grp[Count])
in
    out

 

[PeteyB]

Wauw, ik ben weer super goed heholpen!

@JEC: ik was zó ontzettend omslachtig aan het denken. Kolom erbij om matches te identificeren. Dan voor elke eerste match een nummer 1 toevoegen aan de voorletter, naam enz enz. Dat was uitzichtloos*

@ Ahulpje:
Jij ook heel erg bedankt. Ik heb je VBA code nu niet nodig, maar ik houd de code nog wel ff achter de hand.

Heel erg bedankt alle 2,

Met vriendelijke groet,
Peter

 

[PeteyB]

Edit: na wat langer turen naar de outliers, bleek dat mijn JSON bestand niet helemaal schoon was
nogmaals heel veel dank.

hoi allemaal, ik wil nog even terug komen op bovenstaande, maar wil zeker niet ondankbaar zijn.

nadat ik mijn data de wat uitvoeriger heb geanalyseerd merk ik het soms voor komt dat er personen soms niet achter elkaar komen te staan. Er zijn dan adressen met 7 personen waardoor er een hoop kolommen worden aangemaakt. en adressen met maar 3 personen, die dan niet in de eerste 3 sets kolommen voorkomen, maar enkele kolommen over slaat.

Ik denk dat dit komt doordat het script dynamisch werkt. Wat ik zeker weet is dat er niet meer dan 7 personen op 1 adres komen. Dus er mogen max 7x het aantal kolommen worden aangemaakt. Is het dan misschien toch een beter idee om alle eerste alle dubbele adressen te identificeren als 1, 2, 3, 4, 5, 6 en 7? En dan daarna kolommen worden gemaakt genaamd:
1adres
1huisnr
1geslacht
1voorletter
1voornaam
1achternaam
1nationaliteit
waarna er een 2e reeks kolommen komt genaamd:
2adres
2huisnr
2geslacht
2voorletter
2voornaam
2achternaam
2nationaliteit

enz enz.

ik heb het geprobeerd via ChatGPT. maar die kon mij niet veel verder op weg helpen.
hoop dat jullie mij hierin verder kunnen helpen.

 

[JEC.]

De JSON output eerst opschonen voor het runnen van de query? Moeilijk te zeggen zonder een voorbeeld