Joint 3 tables with 2 relationship

[Anonymous]

Hi, I have 3 tables:

1. Matching indicator

Ref No.1	Ref No.2
C0001	A0001
C0001	A0002
C0002	A0003

2. Table small

SKU number	Ref No.2	Qty
1022-00200-5118-34-10	A0001	1
1022-00200-5118-36-10	A0002	2
1022-00200-5118-34-10	A0003	3

3. Table big

SKU	Qty	Ref No.1
1022-00200-5118-34-10	1	C0001
1022-00200-5118-36-10	2	C0001
1022-00200-5118-34-10	4	C0002

How can I join them together and come up with table like this:

Ref No.1	Ref No.2	SKU number	Small	Big
C0001	A0001	1022-00200-5118-34-10	1	1
C0001	A0002	1022-00200-5118-36-10	2	2
C0002	A0003	1022-00200-5118-34-10	3	4

I tried to make like this:

I want to make like this:

But I got warning:

May I know how to achieve that?

Thanks

[Thim]

instead of setting up relations to each table, you could go under Quiry Editor and Merge the tables.

I would use table 2 as base, since this is the table that has relations to the other 2 tables.

Click Quiry Editor (Edit Quirres)

Then, on the left side, click on table 2

Then on top right, there is a button called "Merge Queries" Click this.

For table one choose the field "Ref No.2" for both table as the matchin coloumn.

For the three, choose the field "SKU" for both table as the matchin coloumn.

Then you can just expand the tables, and all is now collected on table 2.

Hope this helps. 🙂

[Anonymous]

Hi @Thim  Thans,

After merge, I should have 5 columns,right?

However I dont see it after save & close?

May I know how to use the merged table?

[Anonymous]

@Thim  One more question, does this merging achieve following situation as well?

1. Matching indicator

Ref No.1	Ref No.2
C0001	A0001
C0001	A0002
C0002	A0003
C0002	A0004

2. Table small

SKU number	Ref No.2	Qty
1022-00200-5118-34-10	A0001	1
1022-00200-5118-36-10	A0002	2
1022-00200-5118-34-10	A0003	3
1022-00200-5118-35-10	A0004	2

3. Table big

SKU	Qty	Ref No.1
1022-00200-5118-34-10	1	C0001
1022-00200-5118-36-10	2	C0001
1022-00200-5118-34-10	4	C0002
1022-00200-5118-37-10	2	C0002

How can I join them together and come up with table like this:

Ref No.1	Ref No.2	SKU number	Small	Big
C0001	A0001	1022-00200-5118-34-10	1	1
C0001	A0002	1022-00200-5118-36-10	2	2
C0002	A0003	1022-00200-5118-34-10	3	4
C0002	A0004	1022-00200-5118-35-10	2
C0002		1022-00200-5118-37-10		2

Thanks a lot!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can get a merged table in power query as below.

M code for your reference.

let
    Source = Table.NestedJoin(Big, {"SKU"}, Small, {"SKU"}, "Small", JoinKind.FullOuter),
    #"Expanded Small" = Table.ExpandTableColumn(Source, "Small", {"SKU", "Ref No.2", "Qty"}, {"Small.SKU", "Small.Ref No.2", "Small.Qty"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Small", {"Small.Ref No.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each if [SKU] = null then [Small.SKU] else [SKU]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SKU"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Qty", "Ref No.1", "Small.SKU", "Small.Ref No.2", "Small.Qty"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"Small.Ref No.2"}, #"Matching indicator", {"Ref No.2"}, "Matching indicator", JoinKind.LeftOuter),
    #"Expanded Matching indicator" = Table.ExpandTableColumn(#"Merged Queries", "Matching indicator", {"Ref No.1"}, {"Matching indicator.Ref No.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Matching indicator", "Custom.1", each if [Matching indicator.Ref No.1] = null then [Ref No.1] else [Matching indicator.Ref No.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Matching indicator.Ref No.1", "Ref No.1"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Custom.1", "Small.Ref No.2", "Qty", "Small.SKU", "Small.Qty"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns1",{"Qty", "Small.SKU", "Small.Qty"}),
    #"Merged Queries1" = Table.NestedJoin(#"Removed Columns2", {"Custom", "Small.Ref No.2"}, Small, {"SKU", "Ref No.2"}, "Small", JoinKind.LeftOuter),
    #"Expanded Small1" = Table.ExpandTableColumn(#"Merged Queries1", "Small", {"Qty"}, {"Small.Qty"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded Small1", {"Custom", "Custom.1"}, Big, {"SKU", "Ref No.1"}, "Big", JoinKind.LeftOuter),
    #"Expanded Big" = Table.ExpandTableColumn(#"Merged Queries2", "Big", {"Qty"}, {"Big.Qty"})
in
    #"Expanded Big"

Pbix as attached.

[Anonymous]

@v-frfei-msft  Hi Frank, Thanks for your answer.

I was replicating your solution and found that Im too simplified the questions.

Reason is that in your answer, you remove the duplicate item in "Small.Ref No.2"

However in the real data

1. each "Small.Ref No.2" has multiple products as well,

2. One "Ref No.1", may contain different "Ref No.2", with same products 

Is Power BI able to do that?

Thanks a lot

1. Matching indicator

Ref No.1	Ref No.2
C0001	A0001
C0001	A0002
C0002	A0003
C0002	A0004

2. Table small

SKU number	Ref No.2	Qty
1022-00200-5118-34-10	A0001	1
1022-00200-5118-36-10	A0001	2
1022-00200-5118-36-10	A0002	2
1022-00200-5118-34-10	A0003	3
1022-00200-5118-35-10	A0004	2

3. Table big

SKU	Qty	Ref No.1
1022-00200-5118-34-10	1	C0001
1022-00200-5118-36-10	4	C0001
1022-00200-5118-34-10	4	C0002
1022-00200-5118-37-10	2	C0002

Ideal Result:

Ref No.1	Ref No.2	SKU number	Small	Big
C0001	A0001	1022-00200-5118-34-10	1	1
C0001	A0001	1022-00200-5118-36-10	2	2
C0001	A0002	1022-00200-5118-36-10	2	2
C0002	A0003	1022-00200-5118-34-10	3	4
C0002	A0004	1022-00200-5118-35-10	2
C0002		1022-00200-5118-37-10		2