Replace values in up to 2 columns based on table values

[ktbb5757]

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!

[dufoq3]

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

[ktbb5757]

That worked. Thanks so much.

[Anonymous · Accepted solution]

Hi @ktbb5757 ，
You can try the follwing code

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("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
    FinalTable

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

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.