Mergin a single column in table X with multiple columns table Y - Need help with M/SQL code

[Anonymous]

Hi all, 

I have two tables, X and Y.

Table X contains four different levels of code. 1 digit codes, 2 digit codes, 3 digit codes and 4 digit codes. (levels of company hierarchy) looks like this:

Code	Name
1551	LOL
12	CAT
123	DOG
1568	RABBIT
10	HORSE
156	DONKEY

Table Y contains the code from table X, but has no names. I need to merge the two tables together to bring the names into table Y. 

level 1	level 2	level 3	level 4
2	23	153	1008
7	25	213	2568
4	84	689	8548
8	99	987	9875
9	78	458	4153

I could repeat using the merge function, this works, but I feel like it might not be very inneficient?

My source is SQL Database, and I use an import connection. Schould I do this in SQL, tailormade "M" code, or by using the "Merge Queries" button several times? 

What is most efficient and what would the SQL or M code look like? 

Thank you!

Br,

Morten

[Anonymous]

None of the codes in Table Y is there in Table X. Why?

[TeigeGao]

Hi @Anonymous ,

Could you please share us your expected result?

Best Regards,

Teige

[Anonymous]

Just to clarify what I want: 

I have table X and Y

X 

Code	Name
1551	LOL
12	CAT
123	DOG
1568	RABBIT
10	HORSE
156	DONKEY
2	SHARK
7	EAGLE
4	LEOPARD
84	SQUID
689	LADY BUG
1551	CHICKEN

Y

level 1	level 2	level 3	level 4
2	12	156	1568
7	10	123	2568
4	84	689	1551

And I want table Z

level 1	Level 1 Name	level 2	Level 2 Name	level 3	Level 3 Name	level 4	Level 4 Name
2	SHARK	12	CAT	156	DONKEY	1568	RABBIT
7	EAGLE	10	HORSE	123	DOG	2568	CHICKEN
4	LEOPARD	84	SQUID	689	LADY BUG	1551	LOL

I have found that I can use the merge function several times but I suspect that is not the best for performance but maybe I'm wrong? Do other people have suggestions for a more elegant solution? 🙂

[Anonymous]

Try this Power Query

let

//Get X Table
XTable = let
    Source = Sql.Database(<sqlserver>, "Sandbox"),
    dbo_X = Source{[Schema="dbo",Item="X"]}[Data]
in
    dbo_X,

//Get Y Table
YTable = let
    Source = Sql.Database(<sqlserver>, "Sandbox"),
    dbo_Y = Source{[Schema="dbo",Item="Y"]}[Data]
in
    dbo_Y,


//Function for getting the name
getName = (SearchCode as text, Masterlist as table) as text => 
List.First(
        Table.Column(Table.SelectRows(XTable,each Record.Field(_,"Code")=SearchCode),"Name")
    ),

Result = YTable,
AddLevel1Names = Table.AddColumn(Result,"Level 1 Name", each getName([level 1],XTable)),
AddLevel2Names = Table.AddColumn(AddLevel1Names,"Level 2 Name", each getName([level 2],XTable)),
AddLevel3Names = Table.AddColumn(AddLevel2Names,"Level 3 Name", each getName([level 3],XTable)),
AddLevel4Names = Table.AddColumn(AddLevel3Names,"Level 4 Name", each getName([level 4],XTable)),
RearrangeColumns = Table.ReorderColumns(AddLevel4Names,{"level 1","Level 1 Name","level 2","Level 2 Name","level 3","Level 3 Name","level 4","Level 4 Name"})
in
    RearrangeColumns

Replace <sqlserver> with your server names.

[Anonymous]

Thank you! Will try it out as soon as possible and then I'll get back to you asap

[Anonymous]

Actually, you could have imported X and Y tables and add the following calculated columns to your Y table. It is much simpler.

Level 1 Name = LOOKUPVALUE(X[Name],X[Code],Y[level 1])

Level 2 Name = LOOKUPVALUE(X[Name],X[Code],Y[level 2])

Level 3 Name = LOOKUPVALUE(X[Name],X[Code],Y[level 3])

Level 4 Name = LOOKUPVALUE(X[Name],X[Code],Y[level 4])

[Zubair_Muhammad]

@Anonymous 

Seems you can unpivot the levels in TableY and then merge with TableX.

Then pivot back the levels