Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
None of the codes in Table Y is there in Table X. Why?
Hi @Anonymous ,
Could you please share us your expected result?
Best Regards,
Teige
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? 🙂
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.
Thank you! Will try it out as soon as possible and then I'll get back to you asap
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])
@Anonymous
Seems you can unpivot the levels in TableY and then merge with TableX.
Then pivot back the levels
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |