Forum Discussion
Replace values in matrix/table based on reference table
- 1 year ago
Just copy the next code and past it into advance editor.
Summery:
Step 1: Unpviot table 1Step 2: Merge the Table 2 and result of unpviot table 1
Step 3: remove extra column
Step 4: Pivot the result table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgRiIyA2VorVgQo7AbmmQGwGxOYIYWcg1wKILUG6DJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, #"round 1" = _t, #"round 2" = _t, #"round 3" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Area"}, "Attribute", "Value"), Source2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgJiQ3OlWB2omBOQbwoSN0QVMwepM0GIOQP5lkBsbKYUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, Cluster = _t, Count = _t]), #"Merged Queries" = Table.NestedJoin(Source2, {"Area", "Cluster"}, #"Unpivoted Other Columns", {"Area", "Value"}, "New", JoinKind.LeftOuter), #"Expanded New" = Table.ExpandTableColumn(#"Merged Queries", "New", {"Attribute", "Value"}, {"Attribute", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded New",{"Cluster", "Value"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute", Order.Ascending}}), #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Count") in #"Pivoted Column"result
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
Just copy the next code and past it into advance editor.
Summery:
Step 1: Unpviot table 1
Step 2: Merge the Table 2 and result of unpviot table 1
Step 3: remove extra column
Step 4: Pivot the result table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgRiIyA2VorVgQo7AbmmQGwGxOYIYWcg1wKILUG6DJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, #"round 1" = _t, #"round 2" = _t, #"round 3" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Area"}, "Attribute", "Value"),
Source2= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixKTVRwVNJRMgJiQ3OlWB2omBOQbwoSN0QVMwepM0GIOQP5lkBsbKYUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, Cluster = _t, Count = _t]),
#"Merged Queries" = Table.NestedJoin(Source2, {"Area", "Cluster"}, #"Unpivoted Other Columns", {"Area", "Value"}, "New", JoinKind.LeftOuter),
#"Expanded New" = Table.ExpandTableColumn(#"Merged Queries", "New", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded New",{"Cluster", "Value"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Count")
in
#"Pivoted Column"
result
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos.
Thank you!
- Mants1 year agoFrequent Visitor
Thank you Omid, much appreciated this helps!