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

Item No	Colour	Qty	Supplier
A123	Blue	1	S123
B123	Pink	3	S456
A123	Blue	4	S123
C123	White	3	S456
D123	Green	2	S456
D123	Pink	1	S123

<tbody>
</tbody>

Table B

Product No	Colour Name	Quantity	Customer
A123	Blue	3	ABC
B123	Black	4	CDE
A123	Blue	5	FEG
C123	White	2	ABC
D123	Green	3	FEG
D123	Grey	1	CDE

<tbody>
</tbody>

Results

Product No	Colour Name	Table A	Table B	Variance
A123	Blue	5	8	-3
B123	Black	0	4	-4
B123	Pink	3	0	3
C123	White	3	2	1
D123	Green	2	3	-1
D123	Grey	0	1	-1
D123	Pink	1	0	1

<tbody>
</tbody>

 

[CyrusTheVirus]

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

Item No	Colour	Qty - Table A	Supplier		Item No	Colour	Qty - Table B	Customer
A123	Blue	1	S123		A123	Blue	3	ABC
B123	Pink	3	S456		B123	Black	4	CDE
A123	Blue	4	S123		A123	Blue	5	FEG
C123	White	3	S456		C123	White	2	ABC
D123	Green	2	S456		D123	Green	3	FEG
D123	Pink	1	S123		D123	Grey	1	CDE

<tbody>
</tbody>

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.

Item No	Colour	Table A	Table B	Variance
A123	Blue	5	8	-3
B123	Black	0	4	-4
B123	Pink	3	0	3
C123	White	3	2	1
D123	Green	2	3	-1
D123	Grey	0	1	-1
D123	Pink	1	0	1

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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]

TableA

TableB

Result

Item No

Colour

Qty

Supplier

Item No

Colour Name

Quantity

Customer

Item No

Colour

TableA

TableB

Variance

A123

Blue

1​

S123

A123

Blue

3​

ABC

A123

Blue

5​

8​

-3​

B123

Pink

3​

S456

B123

Black

4​

CDE

B123

Black

0​

4​

-4​

A123

Blue

4​

S123

A123

Blue

5​

FEG

B123

Pink

3​

0​

3​

C123

White

3​

S456

C123

White

2​

ABC

C123

White

3​

2​

1​

D123

Green

2​

S456

D123

Green

3​

FEG

D123

Grey

0​

1​

-1​

D123

Pink

1​

S123

D123

Grey

1​

CDE

D123

Green

2​

3​

-1​

D123

Pink

1​

0​

1​