Forum Discussion
Data Transformation challenge.. Replace values from another Table, with specific condition
Hello dear PBI Community,
I'm struggling with the following transformation..
I have two Tables, The one on top contains the Values I'm trying to get onto the second Table ( column1, Group 0, Group 1 ), this table doesn't get updated.
For each row in the second Table below, for the "Group 0", the values need to be compared to the "Group" &"Number 0" column ( Yellow Table ), and replace it with its corresponding numerical value. Same for "Group 1" / "Number 1", to be at the end able to see the same end result table
What is the best optimal way to achieve this transformation, knowing that my ( Column1, Group 0, Group 1 ) Table, is being updated with new rows on a weekly basis.
Thank you all for the help
Hi, Vrykolakas ;
Try it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}), Custom1 = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {List.Zip({#"Table1"[Group],#"Table1"[Number 0]}),List.Zip({#"Table1"[Group],#"Table1"[Number 1]})} ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.ReplaceMatchingItems({[Group 0]},[Custom]{0}){0} ), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.ReplaceMatchingItems({[Group 1]},[Custom]{1}){0} ) in #"Added Custom2", #"Removed Columns" = Table.RemoveColumns(Custom1,{"Group 0", "Group 1", "Custom"}) in #"Removed Columns"The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Vrykolakas ;
Try it.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}), Custom1 = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLsnMS1fSUXICYkelWB1kMWcgRhMCq0QVcsQi5gLGmOqw2eCsFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Group 0" = _t, #"Group 1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Group 0", type text}, {"Group 1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {List.Zip({#"Table1"[Group],#"Table1"[Number 0]}),List.Zip({#"Table1"[Group],#"Table1"[Number 1]})} ), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.ReplaceMatchingItems({[Group 0]},[Custom]{0}){0} ), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.ReplaceMatchingItems({[Group 1]},[Custom]{1}){0} ) in #"Added Custom2", #"Removed Columns" = Table.RemoveColumns(Custom1,{"Group 0", "Group 1", "Custom"}) in #"Removed Columns"The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.=let a=Table.Buffer(Table1) in #table(Table.ColumnNames(Table2),Table.ToList(Table2,each List.Transform(List.Positions(_),(x)=>if x=0 then _{0} else Record.FieldOrDefault(a{[Group=_{x}]}?,"Number"&Text.From(x),null))))
