PQ: append tables and get unique values

[illusionek]

Hi Guys

Please could you help me to develop this solution in the Power Query. I believe I understand steps I need to go through or at least this is how I would do it in MS Access but not sure how to do it in PQ as I am really new to this. I especially struggle with Steps 4, 5, 6


1 Bring all tables into the data model
2 Group by Product/Item No & Colour to get total quantity for each as there may be duplicates in each column
3 Append both tables for Product/Item No and Colour
4 ‘Select Distinct’ from appended table to get unique list of Product/Item Nos and Colours
5 Create new table with the list from Step 4
6 Create a link between table from Step 5 and tables from Step 2. Link is based on the combination of Product/Item No & Colour (ie concatenate)
7 Bring qtys from both columns
8 Create function to deduct one from the other



Table A

[TABLE="width: 185"]
<tbody>[TR]
[TD]Item No[/TD]
[TD]Colour[/TD]
[TD]Qty[/TD]
[TD]Supplier[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]1[/TD]
[TD]S123[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Pink[/TD]
[TD="align: right"]3[/TD]
[TD]S456[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]4[/TD]
[TD]S123[/TD]
[/TR]
[TR]
[TD]C123[/TD]
[TD]White[/TD]
[TD="align: right"]3[/TD]
[TD]S456[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Green[/TD]
[TD="align: right"]2[/TD]
[TD]S456[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Pink[/TD]
[TD="align: right"]1[/TD]
[TD]S123[/TD]
[/TR]
</tbody>[/TABLE]

Table B

[TABLE="width: 284"]
<tbody>[TR]
[TD]Product No[/TD]
[TD]Colour Name[/TD]
[TD]Quantity[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]3[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Black[/TD]
[TD="align: right"]4[/TD]
[TD]CDE[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]5[/TD]
[TD]FEG[/TD]
[/TR]
[TR]
[TD]C123[/TD]
[TD]White[/TD]
[TD="align: right"]2[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Green[/TD]
[TD="align: right"]3[/TD]
[TD]FEG[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Grey[/TD]
[TD="align: right"]1[/TD]
[TD]CDE[/TD]
[/TR]
</tbody>[/TABLE]

Results

[TABLE="width: 321"]
<tbody>[TR]
[TD]Product No[/TD]
[TD]Colour Name[/TD]
[TD]Table A[/TD]
[TD]Table B[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Black[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-4[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Pink[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C123[/TD]
[TD]White[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Green[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Grey[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Pink[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Make sure your headers are somewhat consistent. Change them to the below and follow the steps. Reach out with questions.

[TABLE="width: 635"]
<tbody>[TR]
[TD]Item No[/TD]
[TD]Colour[/TD]
[TD]Qty - Table A[/TD]
[TD]Supplier[/TD]
[TD][/TD]
[TD]Item No[/TD]
[TD]Colour[/TD]
[TD]Qty - Table B[/TD]
[TD]Customer[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]1[/TD]
[TD]S123[/TD]
[TD][/TD]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]3[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Pink[/TD]
[TD="align: right"]3[/TD]
[TD]S456[/TD]
[TD][/TD]
[TD]B123[/TD]
[TD]Black[/TD]
[TD="align: right"]4[/TD]
[TD]CDE[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]4[/TD]
[TD]S123[/TD]
[TD][/TD]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]5[/TD]
[TD]FEG[/TD]
[/TR]
[TR]
[TD]C123[/TD]
[TD]White[/TD]
[TD="align: right"]3[/TD]
[TD]S456[/TD]
[TD][/TD]
[TD]C123[/TD]
[TD]White[/TD]
[TD="align: right"]2[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Green[/TD]
[TD="align: right"]2[/TD]
[TD]S456[/TD]
[TD][/TD]
[TD]D123[/TD]
[TD]Green[/TD]
[TD="align: right"]3[/TD]
[TD]FEG[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Pink[/TD]
[TD="align: right"]1[/TD]
[TD]S123[/TD]
[TD][/TD]
[TD]D123[/TD]
[TD]Grey[/TD]
[TD="align: right"]1[/TD]
[TD]CDE[/TD]
[/TR]
</tbody>[/TABLE]


Step 1) Make both data sets into tables (Ctrl+T), label one 'TableA' and one 'TableB'
Step 2) Click 'TableB' and then click 'Data', then 'From Table/Range', then in power query click 'Close & Load', then 'Close & Load To...', 'Only Create Connection'.
Step 3) Click TableA', and then click 'Data', then 'From Table/Range', then in power query click 'Advanced Editor' and paste the below code into it. Then click ok, then 'Close & Load'.

let    Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item No", type text}, {"Colour", type text}, {"Qty - Table A", Int64.Type}, {"Supplier", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", TableB}),
    #"Removed Other Columns" = Table.SelectColumns(#"Appended Query",{"Item No", "Colour", "Qty - Table A", "Qty - Table B"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Item No", "Colour"}, {{"Table A", each List.Sum([#"Qty - Table A"]), type number}, {"Table B", each List.Sum([#"Qty - Table B"]), type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Table A", "Table B"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Variance", each [Table A]-[Table B]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Item No", Order.Ascending}, {"Colour", Order.Ascending}})
in
    #"Sorted Rows"

The result should be the below. Alter the headers of the final table if you want.

[TABLE="width: 346"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item No[/TD]
[TD]Colour[/TD]
[TD]Table A[/TD]
[TD]Table B[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD]Blue[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Black[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-4[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD]Pink[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C123[/TD]
[TD]White[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Green[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Grey[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-1[/TD]
[/TR]
[TR]
[TD]D123[/TD]
[TD]Pink[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

another approach

[SIZE=1]// TableA
let
    Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content]

in
    Source[/SIZE]

[SIZE=1]// TableB
let
    Source = Excel.CurrentWorkbook(){[Name="TableB"]}[Content]
in
    Source[/SIZE]

Append Queries as New

[SIZE=1]// Append1
let
    Source = Table.Combine({TableA, TableB}),
    Replace = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{"Qty", "Quantity"}),
    Merge = Table.CombineColumns(Replace,{"Colour", "Colour Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Colour.1"),
    Group = Table.Group(Merge, {"Item No", "Colour.1"}, {{"TableA", each List.Sum([Qty]), type number}, {"TableB", each List.Sum([Quantity]), type number}}),
    Subtraction = Table.AddColumn(Group, "Subtraction", each [TableA] - [TableB], type number),
    Rename = Table.RenameColumns(Subtraction,{{"Colour.1", "Colour"}, {"Subtraction", "Variance"}}),
    Sort = Table.Sort(Rename,{{"Item No", Order.Ascending}})
in
    Sort[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]TableA[/td][td][/td][td][/td][td][/td][td][/td][td]TableB[/td][td][/td][td][/td][td][/td][td][/td][td]Result[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Item No[/td][td=bgcolor:#5B9BD5]Colour[/td][td=bgcolor:#5B9BD5]Qty[/td][td=bgcolor:#5B9BD5]Supplier[/td][td][/td][td=bgcolor:#5B9BD5]Item No[/td][td=bgcolor:#5B9BD5]Colour Name[/td][td=bgcolor:#5B9BD5]Quantity[/td][td=bgcolor:#5B9BD5]Customer[/td][td][/td][td=bgcolor:#70AD47]Item No[/td][td=bgcolor:#70AD47]Colour[/td][td=bgcolor:#70AD47]TableA[/td][td=bgcolor:#70AD47]TableB[/td][td=bgcolor:#70AD47]Variance[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A123[/td][td=bgcolor:#DDEBF7]Blue[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]S123[/td][td][/td][td=bgcolor:#DDEBF7]A123[/td][td=bgcolor:#DDEBF7]Blue[/td][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]ABC[/td][td][/td][td=bgcolor:#E2EFDA]A123[/td][td=bgcolor:#E2EFDA]Blue[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

8​

[/td][td=bgcolor:#E2EFDA]

-3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B123[/td][td]Pink[/td][td]

3​

[/td][td]S456[/td][td][/td][td]B123[/td][td]Black[/td][td]

4​

[/td][td]CDE[/td][td][/td][td]B123[/td][td]Black[/td][td]

0​

[/td][td]

4​

[/td][td]

-4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A123[/td][td=bgcolor:#DDEBF7]Blue[/td][td=bgcolor:#DDEBF7]

4​

[/td][td=bgcolor:#DDEBF7]S123[/td][td][/td][td=bgcolor:#DDEBF7]A123[/td][td=bgcolor:#DDEBF7]Blue[/td][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]FEG[/td][td][/td][td=bgcolor:#E2EFDA]B123[/td][td=bgcolor:#E2EFDA]Pink[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C123[/td][td]White[/td][td]

3​

[/td][td]S456[/td][td][/td][td]C123[/td][td]White[/td][td]

2​

[/td][td]ABC[/td][td][/td][td]C123[/td][td]White[/td][td]

3​

[/td][td]

2​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]D123[/td][td=bgcolor:#DDEBF7]Green[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]S456[/td][td][/td][td=bgcolor:#DDEBF7]D123[/td][td=bgcolor:#DDEBF7]Green[/td][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]FEG[/td][td][/td][td=bgcolor:#E2EFDA]D123[/td][td=bgcolor:#E2EFDA]Grey[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

-1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D123[/td][td]Pink[/td][td]

1​

[/td][td]S123[/td][td][/td][td]D123[/td][td]Grey[/td][td]

1​

[/td][td]CDE[/td][td][/td][td]D123[/td][td]Green[/td][td]

2​

[/td][td]

3​

[/td][td]

-1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]D123[/td][td=bgcolor:#E2EFDA]Pink[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]
[/table]