merging tables and expanding results as new column

[ValeriaBreve]

Hello,

I need to join 2 tables and assign the results fo the second tables as columns in my first table:

First Table:

ID	Sales
1	20
2	56
3	41
4	54
5	45

Second Table:

ID	Order Number	Quantity
1	ORDER1	10
2	ORDER2	20
3	ORDER3	40
4	ORDER4	54
5	ORDER5	45
1	ORDER6	10
2	ORDER7	36
3	ORDER8	5

Result Table:

ID	Sales	Order Number 1	Order Number 2	Quantity 1	Quantity 2
1	20	ORDER1	ORDER6	10	10
2	56	ORDER2	ORDER7	20	36
3	41	ORDER3	ORDER8	40	5
4	54	ORDER4		54
5	45	ORDER5		45

of course the number of generated columns needs to be dynamic.

I was wondering what is the most efficient to do this in Powerquery - know that performance is important here (I have a large amount of data).

Thanks!

kind regards

Valeria

[ValeriaBreve]

@AlB I know 🙂 but in my case it is not that simple... I made the example easy, but in reality this is regarding orders and suborders. The orders should be uniquely identified as they are exported from Powerbi to a paginated report that is used on another system (so no duplicated present, which would be if I did a simple merge). Also, the orders are used as unique ID on a Gantt chart in PowerBI to visualize them - the suborders are combined as tooltip with their different attributes. I have not found a way to overcome this structure, I have done the columns in DAX in the past but I was wodnering whether PowerQuery would be more suitable... Thanks!

[AlB · Accepted solution]

@ValeriaBreve 

I still think that there probably is a more efficient structure for the table but here is an attempt to what you are looking for. It's not fully straightforward so not too sure about performance.See it all at work in the attached file.

Table1 and Table2 are your initial tables. First you create an ancillary table Table2_Modified based on Table2:

let
    Source = Table2,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Order Number", type text}, {"Quantity", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Order Number", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"AllRows", each _, type table [ID=nullable number, Order Number=nullable text, Quantity=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows], "Index",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Order Number", "Quantity", "Index"}, {"ID.1", "Order Number", "Quantity", "Index"}),
    T1_0 = Table.SelectColumns(#"Expanded Custom", {"ID", "Order Number", "Index"}),
    T1_1 =Table.AddColumn(T1_0, "Custom", each "Order number " & Text.From([Index]), type text),
    #"Removed Columns2" = Table.RemoveColumns(T1_1,{"Index"}),
    T2_0 = Table.SelectColumns(#"Expanded Custom", {"ID", "Quantity", "Index"}),
    T2_1 =Table.AddColumn(T2_0, "Custom", each "Quantity " & Text.From([Index]), type text),
    #"Removed Columns3" = Table.RemoveColumns(T2_1,{"Index"}),
    T1_2 = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Custom]), "Custom", "Order Number"),
    T2_2 = Table.Pivot(#"Removed Columns3", List.Distinct(#"Removed Columns3"[Custom]), "Custom", "Quantity"),
    #"Merged Queries" = Table.NestedJoin(T1_2, {"ID"}, T2_2, {"ID"}, "Result_", JoinKind.LeftOuter),
    #"Expanded Result_" = Table.ExpandTableColumn(#"Merged Queries", "Result_", {"Quantity 1", "Quantity 2"}, {"Quantity 1", "Quantity 2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Result_",{{"Order number 1", type text}, {"Order number 2", type text}, {"Quantity 1", Int64.Type}, {"Quantity 2", Int64.Type}})

in
    #"Changed Type1"

  Then you merge that with Table1:

let
    Source = Table.NestedJoin(Table1, {"ID"}, Table2_Modified, {"ID"}, "Table2Mod", JoinKind.LeftOuter),
    #"Expanded Table2Mod" = Table.ExpandTableColumn(Source, "Table2Mod", {"Order number 1", "Order number 2", "Quantity 1", "Quantity 2"}, {"Order number 1", "Order number 2", "Quantity 1", "Quantity 2"})
in
    #"Expanded Table2Mod"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[ValeriaBreve]

@AlB Thank you!

I was going in the same direction, only in my case I tried to generate columns directly without pivoting:

= List.Accumulate({1..MaxCol}, #"Added Index", (state, current) =>
Table.AddColumn(state, "PO_FINI_" & Number.ToText(current), each if[Rank]=current then [Order Number] else null))

(where MaxCol is = List.Max(#"Added Index"[Rank]))

the drawback of this way is that the values get written in the same row considered in the context, so they would need to further transformed to go up n number of rows (depending on the index), which I still have not found a good way to do....

Thanks 

Kind regards

Valeria

[AlB]

Hi @ValeriaBreve 

Why do you want that structure (Result) for your table? It will not lend itself well to DAX code. What do you ultimately want to do?

It would be much easier to have just four columns

ID

Sales

Order Number

Quantity

which you can do with a simple merge

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.