Forum Discussion
Replace values in up to 2 columns based on table values
Hi all - Relatively new user here.
I have two columns in table "Main." One is "Issue Type" and the other is "Subcategory." The underly data is part of a investigation case management system where each case is assigned an issue type and then a subcategory. Some of these values have changed over the years and we want to update the older values with the new ones.
I have mapped all the old values to their new corresponding value in a separate table (Issue Map). My data might look something like this:
Depending on each situation, I might need to change the issue type, the subcategory, both, or neither.
I've not been able to locate a solution online for this, but I'm guess that has more to do with me asking the wrong question. Any help would be greatly appreciated.
Thank you!
Hi ktbb5757 ，
You can try the follwing codelet MainTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYw7CsNADAWvsqj2KfIjIaRJysXFWmhBWJaCVi58e29M2nkzL2c4OU/kGwxwXb5iG1F6Ms5TwRnGIcPZtApjJKvpoUFOLbr84oYkUpRsbYf4IVyd4/d0K9K4MpZg02O8vzu+9MZ5Yf3zcQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prior Issue Type" = _t, #"Prior Subcategory" = _t]), IssueMapTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspMSi2qVNJRcs7PzU0tykxOzFGAisXqRCt5BAGlAlKLivPzgBJBqTmJJZn5ecUZmQXFCHm3xJzizDSgVpCcUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"New Issue Type" = _t, #"New Subcategory" = _t]), MainWithIndex = Table.AddIndexColumn(MainTable, "Index", 0, 1, Int64.Type), IssueMapWithIndex = Table.AddIndexColumn(IssueMapTable, "Index", 0, 1, Int64.Type), MergedTables = Table.NestedJoin(MainWithIndex, "Index", IssueMapWithIndex, "Index", "IssueMap", JoinKind.FullOuter), ExpandedTable = Table.ExpandTableColumn(MergedTables, "IssueMap", {"New Issue Type", "New Subcategory"}), ReplacedTable = Table.AddColumn(ExpandedTable, "Current Issue Type", each if [New Issue Type] = null then [Prior Issue Type] else [New Issue Type]), ReplacedTable2 = Table.AddColumn(ReplacedTable, "Current Subcategory", each if [New Subcategory] = null then [Prior Subcategory] else [New Subcategory]), FinalTable = Table.SelectColumns(ReplacedTable2, {"Current Issue Type", "Current Subcategory"}) in FinalTableFinal output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
- dufoq3
Community Champion
Hi ktbb5757, different approach:
Output
let MainTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYw7CsNADAWvsqj2KfIjIaRJysXFWmhBWJaCVi58e29M2nkzL2c4OU/kGwxwXb5iG1F6Ms5TwRnGIcPZtApjJKvpoUFOLbr84oYkUpRsbYf4IVyd4/d0K9K4MpZg02O8vzu+9MZ5Yf3zcQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prior Issue Type" = _t, #"Prior Subcategory" = _t]), IssueMapTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcirKTEotqlTSUXLOz81NLcpMTsxRcIKIxepEK3kEAaUCUouK8/OAEkGpOYklmfl5xRmZBcUIebfEnOLMNKBWkJxSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"New Issue Type" = _t, #"New Subcategory" = _t]), MainTableReplaced = Table.FromColumns(List.Transform(List.Zip({Table.ToColumns(MainTable), Table.ToColumns(IssueMapTable)}), each List.Transform(List.Zip(_), each List.Last(List.RemoveNulls(_)))), Value.Type(Table.FirstN(MainTable, 0))) in MainTableReplaced - lbendlin
Super User
The most straightforward approach would be to include your reference table in your data model. That would make it simple to swap the old names for the new names, for example via COALESCE.
Hi ktbb5757 ，
You can try the follwing codelet MainTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYw7CsNADAWvsqj2KfIjIaRJysXFWmhBWJaCVi58e29M2nkzL2c4OU/kGwxwXb5iG1F6Ms5TwRnGIcPZtApjJKvpoUFOLbr84oYkUpRsbYf4IVyd4/d0K9K4MpZg02O8vzu+9MZ5Yf3zcQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prior Issue Type" = _t, #"Prior Subcategory" = _t]), IssueMapTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspMSi2qVNJRcs7PzU0tykxOzFGAisXqRCt5BAGlAlKLivPzgBJBqTmJJZn5ecUZmQXFCHm3xJzizDSgVpCcUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"New Issue Type" = _t, #"New Subcategory" = _t]), MainWithIndex = Table.AddIndexColumn(MainTable, "Index", 0, 1, Int64.Type), IssueMapWithIndex = Table.AddIndexColumn(IssueMapTable, "Index", 0, 1, Int64.Type), MergedTables = Table.NestedJoin(MainWithIndex, "Index", IssueMapWithIndex, "Index", "IssueMap", JoinKind.FullOuter), ExpandedTable = Table.ExpandTableColumn(MergedTables, "IssueMap", {"New Issue Type", "New Subcategory"}), ReplacedTable = Table.AddColumn(ExpandedTable, "Current Issue Type", each if [New Issue Type] = null then [Prior Issue Type] else [New Issue Type]), ReplacedTable2 = Table.AddColumn(ReplacedTable, "Current Subcategory", each if [New Subcategory] = null then [Prior Subcategory] else [New Subcategory]), FinalTable = Table.SelectColumns(ReplacedTable2, {"Current Issue Type", "Current Subcategory"}) in FinalTableFinal output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
That worked. Thanks so much.
