Opgelost Hulp nodig met power query transposen

[Dreday]

Hallo allemaal,

Ik heb een voorbeeld Excel-bestand met fictieve bedrijfsnamen, contactpersonen en bijbehorende fictieve gegevens van de contactpersonen zoals email, website enzovoort.

In mijn voorbeeld bestand staan alle gegevens per bedrijf in één regel naast elkaar. Dit is ooit via een power query gedaan met behulp van iemand op dit forum (maar niet in het voorbeeld bestand!)

Maar nu moet ik dat proces weer omkeren nadat er data aan is toegevoegd en er een nieuw Excel bestand van is gemaakt. En ik heb geen idee hoe ik dat voor elkaar krijg. Heb zitten stoeien met transposen, maar daar kwam ik niet uit.

Hoe zorg ik ervoor (het liefst weer met power query want dat is supercool), dat alle 2e, 3e, 4e (enz.) contactpersonen op een nieuwe regel komen met ook de bedrijfsnaam er weer voor?

Hopelijk kan iemand mij hiermee helpen, zelf word ik helemaal gek van het handmatig knippen en plakken en zie ik al Excel werkbladen in mijn dromen/nachtmerries...

 

[jverkerk]

Zo ongeveer?
Er hoeft niet getransponeerd te worden.

 

[JEC.]

PQ:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"company"},
      {{"Count", each
          let
             tbl = List.Skip(List.Combine(Table.ToRows(_))),
             emailList = List.Alternate(tbl,2,1,1),
             linkList = List.Alternate(tbl,2,1),
             nameList = List.RemoveItems(tbl,emailList & linkList)
          in
             Table.FromRows(List.Zip({emailList,nameList,linkList}))
       }}
    ),
    expand = Table.ExpandTableColumn(grp, "Count", {"Column1", "Column2", "Column3"}, {"Email", "Naam", "Link"}),
    result = Table.SelectRows(expand, each ([Email] <> null))
in
    result

 

[jverkerk]

Ik dacht al dat ik het niet goed gedaan had.

 

[Dreday]

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], grp = Table.Group(Source, {"company"}, {{"Count", each let tbl = List.Skip(List.Combine(Table.ToRows(_))), emailList = List.Alternate(tbl,2,1,1), linkList = List.Alternate(tbl,2,1), nameList = List.RemoveItems(tbl,emailList & linkList) in Table.FromRows(List.Zip({emailList,nameList,linkList})) }} ), expand = Table.ExpandTableColumn(grp, "Count", {"Column1", "Column2", "Column3"}, {"Email", "Naam", "Link"}), result = Table.SelectRows(expand, each ( <> null)) in result

Wow, dankjewel hiervoor! Stel dat ik straks extra kolommen heb die verplaatst moeten worden. Welke gedeeltes van de query moet ik daarvoor aanpassen?

 

[snb]

Zonder formules, zonder VBA, zonder Let, zonder PQ taal:

 

[peter59]

Of toch met PQ maar nu gebruik gemaakt van de functies zoals die in PQ ingebakken zijn.
Het geheel is nu ook een draaitabel én dynamisch.

 

[JEC.]

Als je straks extra kolommen hebt kun je harde verwijzingen maken in de group stap.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"company"},
      {{"Count", each
          let
             colNames = Table.ColumnNames(_),
             emailList = List.Combine(Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=> Text.Contains(Text.Upper(x),"EMAIL"))))),
             linkList = List.Combine(Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=> Text.Contains(Text.Upper(x),"LINK"))))),
             nameList = List.Combine(Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=> Text.Contains(Text.Upper(x),"NAME")))))
          in         
             Table.FromRows(List.Zip({emailList,nameList,linkList}))
       }}
    ),
    expand = Table.ExpandTableColumn(grp, "Count", {"Column1", "Column2", "Column3"}, {"Email", "Naam", "Link"}),
    result = Table.SelectRows(expand, each ([Email] <> null))
in
    result

 

[JEC.]

Een nettere variant

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    colList = {"EMAIL","NAME", "LINK"},
    colNames = Table.ColumnNames(Source),
    grp = Table.Group(Source, {"company"},
      {{"Count", each
            Table.FromColumns(
                List.Accumulate(colList,{},(s,c)=>
                      s & Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=> Text.Contains(Text.Upper(x),c))))
                )
            )  
       }}
    ),
    expand = Table.ExpandTableColumn(grp, "Count", {"Column1", "Column2", "Column3"}, {"Email", "Naam", "Link"}),
    result = Table.SelectRows(expand, each ([Email] <> null))
in
    result

 

[Dreday]

Dankjewel voor alle hulp tot nu toe allemaal! Ik ben er nu achter gekomen dat mijn voorbeeldbestand te simplistisch was en ik niet in staat ben om de oplossingen toe toepassen op mijn echte excel bestand.

Nu heb ik de laatste nette variant van JEC geprobeerd aan te passen door de namen van de extra kolommen erbij te zetten en toe te passen op onderstaand voorbeeld bestand(met de extra kolommen). Maar ik doe iets niet goed en krijg steeds een leeg tabel

Het is een bestand met fictieve bedrijven en contactpersonen. Per bedrijf zijn er maximaal 6 contactpersonen. Elk contactpersoon heeft 9 kolommen. In het beginpunt staan alle fictieve contactpersonen in één regel naast de bedrijfsgegevens. Op het eindpunt staan de contactpersonen onder elkaar per bedrijf.

Is het nog steeds mogelijk om dit voor elkaar te krijgen met een Power Query?

 

[Dreday]

Dit is hoe ik de nette variant van JEC heb geprobeerd aan te passen, ik denk dat ik onderin bij "result = " sowieso iets anders moet neerzetten bij Email? Of gaat er nog veel meer mis met mijn aanpassingen?

let
Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
colList = {"ABCN","Blok", "Aanhef","Voornaam", "Achternaam", "Functietitel", "Email", "CP eten", "CP drinken"},
colNames = Table.ColumnNames(Source),
grp = Table.Group(Source, {"Bedrijfsnaam"},
{{"Count", each
Table.FromColumns(
List.Accumulate(colList,{},(s,c)=>
s & Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=> Text.Contains(Text.Upper(x),c))))
)
)
}}
),
expand = Table.ExpandTableColumn(grp, "Count", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"ABCN", "Blok", "Aanhef", "Voornaam", "Achternaam", "Functietitel", "Email", "CP eten", "CP drinken"}),
result = Table.SelectRows(expand, each ( <> null))
in
result

 

[JEC.]

Het kan zeker

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    colList = {"ABCN","Blok", "Aanhef","Voornaam", "Achternaam", "Functietitel", "Email", "CP eten", "CP drinken"},
    colNames = Table.ColumnNames(Source),
    grp = Table.Group(Source, List.FirstN(colNames,6),
      {{"Count", each
            Table.FromColumns(
                List.Accumulate(colList,{},(s,c)=>
                      s & Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=>
                    Text.Contains(Text.Upper(x),Text.Upper(Text.AfterDelimiter(c," ",{0,RelativePosition.FromEnd}))))))
                )
            )
       }}
    ),
    expand = Table.ExpandTableColumn(grp, "Count", Table.ColumnNames(grp[Count]{0}), colList),
    result = Table.SelectRows(expand, each ([Email] <> null))
in
    result

 

[Dreday]

Het kan zeker

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    colList = {"ABCN","Blok", "Aanhef","Voornaam", "Achternaam", "Functietitel", "Email", "CP eten", "CP drinken"},
    colNames = Table.ColumnNames(Source),
    grp = Table.Group(Source, List.FirstN(colNames,6),
      {{"Count", each
            Table.FromColumns(
                List.Accumulate(colList,{},(s,c)=>
                      s & Table.ToRows(Table.SelectColumns(_,List.Select(colNames,(x)=>
                    Text.Contains(Text.Upper(x),Text.Upper(Text.AfterDelimiter(c," ",{0,RelativePosition.FromEnd}))))))
                )
            )
       }}
    ),
    expand = Table.ExpandTableColumn(grp, "Count", Table.ColumnNames(grp[Count]{0}), colList),
    result = Table.SelectRows(expand, each ([Email] <> null))
in
    result

Duizend maal dank! Op deze manier is het goed gelukt