Pivot and concatenate Power Query

Question

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!

Could you edit your question and paste the original data in text so we don’t have to retype it again? — Ricardo Diaz

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2021-02-19T10:43:21

Steps:

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

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

3- Use the Extract values on the custom column

4- Remove other columns

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

Pivot and concatenate Power Query

1 Answers