Search code examples
pivotconcatenationpowerquery

Pivot and concatenate Power Query

I'm trying to label my rows and it is a bit complicated for me how to get to the final format.

I have the following format with Power Query:

DOC NUM| TRAN | TYPE | ACCS | Amount| Label 
TypeA  | Code |  SA  | Acc1 |  123  | A     
TypeB  | Code |  SA  | Acc2 |  34   | Q
TypeA  | Code |  SA  | Acc3 |  543  | Y
TypeA  | Code |  SA  | Acc4 |  12   | AW
TypeB  | Code |  SA  | Acc5 |  1    | AX
TypeA  | Code |  SA  | Acc5 |  823  | AX
TypeA  | Code |  SA  | Acc6 |  143  | AZ
TypeB  | Code |  SA  | Acc6 |  113  | AZ

My goal is to get this final format where last column is the concatenate of "Label" column depending on the first 3 columns and sorted by "Accs":

DOC NUM| TRAN | TYPE | Label 
TypeA  | Code |  SA  | AYAWAXAZ     
TypeB  | Code |  SA  | QAXAZ

It is not easy for me, because as you can see number of rows and number of labels are variable. The only solution I was able to get is to just pivot "Accs" with "Label" as values, finish here with power query and in excel in the last column create a concatenate of columns:

DOC NUM| TRAN | TYPE | Acc1 | Acc2 | Acc3 | Acc4 | Acc5 | Acc6 | Label 
TypeA  | Code |  SA  |   A  |      |   Y  |  AW  |  AX  |  AZ  |AYAWAXAZ     
TypeB  | Code |  SA  |      |   Q  |      |      |  AX  |  AZ  |QAXAZ

Then I can copy and paste this table in new sheet and remove the "AccX" columns. This is not the best solution for me as I will have many variable columns (which makes the concatenate formula a bit tedious) and I would like it to be automatic if possible.

Any help is really appreciated! Thank you in advance!

Solution

  • Steps:

    1- Group by the columns and use the All rows operation

    enter image description here

    2- Add a custom column refering to the AllRows column of the previous step and the column you'd like to concatenate

    enter image description here

    3- Use the Extract values on the custom column

    enter image description here

    4- Remove other columns

    enter image description here

    M code:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"DOC NUM", type text}, {"TRAN", type text}, {"TYPE", type text}, {"ACCS", type text}, {"Label", type text}}),
    GroupAll = Table.Group(ChangeType, {"DOC NUM", "TRAN", "TYPE"}, {{"AllRows", each _, type table [DOC NUM=nullable text, #"TRAN"=nullable text, #"TYPE"=nullable text, #"ACCS"=nullable text, #"Amount"=number, #"Label"=nullable text]}}),
    AddCustom = Table.AddColumn(GroupAll, "Custom", each [AllRows][Label]),
    ExpandCol = Table.TransformColumns(AddCustom, {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    RemoveOtherCols = Table.SelectColumns(ExpandCol,{"DOC NUM", "TRAN", "TYPE", "Custom"})
in
    RemoveOtherCols

    Let me know if it works