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 th...
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.
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])