Forum Discussion
Mergin a single column in table X with multiple columns table Y - Need help with M/SQL code
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
7 Replies
- Zubair_Muhammad
Community Champion
Anonymous
Seems you can unpivot the levels in TableY and then merge with TableX.
Then pivot back the levels
TeigeGaoSolution Sage
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 RearrangeColumnsReplace <sqlserver> with your server names.
