deel transponeren in power query

[moensk]

in bijlage klein opzetje gemaakt van huidige en gewenste.
ik heb nu per facturatiecode een lijn doch ik wil alle facturatiecodes op één lijn
er kunnen maximaal 5 facturatiecodes zijn per zending.
graag jullie hulp om dit via power query te realiseren

 

[JEC.]

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    grp = Table.Group(Source, {"opdrachtnr", "referentie", "naam", "gemeente"}, {{"factuurcode", each Text.Combine([factuurcode],";") }, {"postcode", each List.First([postcode])}}),
    xMaxList =  List.Transform({
        1..List.Max(
         Table.AddColumn(grp, "Custom", each
            List.Count(
                Text.PositionOfAny([factuurcode], {";"}, Occurrence.All)
            ))[Custom]
        ) +1
    }, each "factuurcode_" & Text.From(_)),
    split = Table.SplitColumn(grp, "factuurcode", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), xMaxList)
in
    split

 

[moensk]

JEC,
zoals altijd werkt het weer super.
bedankt voor snelle reactie

 

[peter59]

Idd mooi van Jec
Maar is eigenlijk niet geheel correct.
Kijk eens naar de postcodes. Er zijn codes welke tussen wal en schip zijn gevallen

 

[moensk]

sorry dit is mijn fout, met slepen
moest allemaal 2300 zijn
excuses

 

[moensk]

later in de query voeg ik lege regels toe. daar loopt het niet vlot
ik heb het script aangepast in voorbeeld.
ik heb uit de tabel 2 rijen gehaald doch bij verversen blijft hij 5 kolommen factuurcode geven ipv 3
tot aan "delCode" gaat het goed doch de laatste stap niet.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    grp = Table.Group(Source, {"opdrachtnr", "referentie", "naam", "gemeente", "code"}, {{"factuurcode", each Text.Combine([factuurcode],";") }, {"postcode", each List.First([postcode])}}),
    xMaxList =  List.Transform({
        1..List.Max(
         Table.AddColumn(    grp, "Custom", each
            List.Count(
                Text.PositionOfAny([factuurcode], {";"}, Occurrence.All)
            ))[Custom]
        ) +1
    }, each "factuurcode_" & Text.From(_)),
    split = Table.SplitColumn(grp, "factuurcode", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), xMaxList),
    cols = Table.ColumnNames(split),
    Aangepast1 = Table.Group(split, {"code"}, {{"Count", each Table.InsertRows(_,Table.RowCount(_), {Record.FromList(List.Repeat({null},Table.ColumnCount(_)),cols)} )}}),
    delCode = Table.RemoveColumns(Aangepast1,{"code"}),
    uitvouwen = Table.ExpandTableColumn(delCode, "Count", {"opdrachtnr", "referentie", "naam", "gemeente", "code", "factuurcode_1", "factuurcode_2", "factuurcode_3", "factuurcode_4", "factuurcode_5", "postcode"}, {"opdrachtnr", "referentie", "naam", "gemeente", "code", "factuurcode_1", "factuurcode_2", "factuurcode_3", "factuurcode_4", "factuurcode_5", "postcode"})
in
    uitvouwen

 

[peter59]

"factuurcode_1", "factuurcode_2", "factuurcode_3", "factuurcode_4", "factuurcode_5"

Het bovenstaande is een "hard" gecodeerd dus niet flexibel.
Kijk eens of dit je gaat helpen? Hier is genoeg over te vinden als je zoek op expand columns dynamicly.

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

 

[peter59]

Ik heb de query iets uitgebreid.
Een beetje van JEC en een beetje van mij.
Persoonlijk maak ik zoveel als mogelijk van de UserInterface van power query gebruik terwijl JEC v.w.b. de functies veel doet combineren. En dat vind ik persoonlijk heel knap.

 

[moensk]

peter59
thanks - ga de stappen bekijken

 

[moensk]

Uitleg is stappen is super om te volgen, beiden alvast bedankt.
laatste vraag :
als ik de factuurregels verplaats dan werkt het niet meer
1. toevoegen extra lijn dan staan ze niet meer samen
2. een lijn minder (2 ipv 3) dan komt er error.
voorbeeld in bijlage
kan dit ook dynamisch gemaakt worden ?

 

[peter59]

Heb je de goede bijlage toegevoegd?
Ik kan je n.l. niet volgen.

 

[moensk]

ja,
ik heb stap "volgorde" toegevoegd
in het voorbeeld heb ik nu een 4de regel toegevoegd en dan ziet ge dat in de uitkomst
factuurcode 1 - 2- 3 samen staat en factuurcode 4 achteraan
ik had factuurcode 4 graag achter factuurcode 3 gehad
als ik uit de tabel maar 2 rijen heb met factuurcode dan geeft hij error

 

[peter59]

Verwijder die laatste stap "volgorde" maar eens.
Deze heb je "hard" gecodeerd.

 

[moensk]

dan werkte het naar wens
dus dan moet ik volgorde bepalen voor ik de andere stappen zet ?
was benieuwd of dit dynamisch kon daar ik dit ook dan kan gebruiken in andere excels...

 

[peter59]

Kijk eens of de bijlage is wat je bedoeld?
Dat kan mijn inziens niet dynamisch want power query weet natuurlijk niet hoe Moensk het e.e.a. gerangschikt wil hebben.
Dus in andere excel sheets zul je de query dienen aan te passen en natuurlijk ook dien je dan ook rekening te houden met de kolomkopnamen want als deze afwijkt dan krijg je een error.

 

[peter59]

In de link een voorbeeldje hoe toch het e.e.a. "dynamisch" kunt maken.
Nou ja, dynamisch?

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

 

[JEC.]

Je kunt ook de laatste regel zo schrijven om de volgorde aan te houden

volgorde = Table.ReorderColumns(KolommenVerw,{"opdrachtnr", "referentie"} & xMaxList & {"naam", "gemeente", "postcode"})

 

[moensk]

JEC
werkt super !

 

[peter59]

Of als je iedere kolom een andere plaats wilt geven.
Gebruik gemaakt van het you-tube voorbeeldje wat ik eerder in #16 plaatste.

 

[moensk]

thanks peter,
is bruikbaar bij een andere excel die ik heb !