Replacing All Record IDs with Names from a different table

[okombol]

Hello, I need some help with this

I have two queries. One that contains a bunch of record IDs in different columns

Creator	Owner	Closer
6A5667	6A5667	6B2349
62349	62349	62349
68C257	62349	65667

And another that contains the Name codes for those record IDs

RecID	Team Name
6A5667	Team A
6B2349	Team B
68C257	Team C

I would like to make my first query look like this

Creator	Owner	Closer
Team A	Team A	Team B
Team B	Team B	Team B
Team C	Team B	Team A

What is the best way to accomplish that? The actual data has a lot more teams and record IDs. 

My solution right now is doing this for every column but there are lots of columns and doing this dozens of times on my query is a lot of work. 

List.Accumulate(
Table.ToRecords( StandardUserTeam ),
[Owner],
( valueToReplace, replaceOldNewRecord ) =>
Text.Replace(
valueToReplace,
replaceOldNewRecord[RecId],
replaceOldNewRecord[Team]
)
)

[Omid_Motamedise]

hi @okombol 

Does the length of RecID fixed for all the items?

[okombol]

Yes, they are all 32 character mixtures of numbers and capital letters

[jgeddes]

Here is a methodology you may be able to use to speed up the replacement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjM1MzNX0kHQRsYmlkqxOtFQlg4aDZaxMDI1R5YB642NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Creator = _t, Owner = _t, Closer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Creator", Int64.Type}, {"Owner", Int64.Type}, {"Closer", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Replace Value" = Table.TransformColumns(#"Unpivoted Columns", {{"Value", each let key = _ in Record.Field(Table.SelectRows(nameTable, each [RecID] = key){0}, "Team Name"), type text}}),
    #"Grouped Rows" = Table.Group(#"Replace Value", {"Attribute"}, {{"AllRows", each _, type table [Attribute=text, Value=number]}}),
    #"Add Index" = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.AddIndexColumn(_, "Index", 1, 1), type table [Attribute=text, Value=text, Index=Int64.Type]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Add Index", "AllRows", {"Value", "Index"}, {"Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Starting with...

idTable

nameTable

Result...

[okombol]

Is there another way to do this without converting to numbers? My real data's recIds contain numbers and letters

[jgeddes · Accepted solution]

Yes, just remove the step that sets the type to number (or Int64.Type)

let
    nameTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnM0NTMzV9JRCklNzFVwVIrVAYo5GRmbWMLEnCBiFs5GpnB1zkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RecID = _t, #"Team Name" = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnM0NTMzV9JBYjgZGZtYKsXqRMOYOhgMsKSFs5GpOYokxIjYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Creator = _t, Owner = _t, Closer = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Replace Value" = Table.TransformColumns(#"Unpivoted Columns", {{"Value", each let key = _ in Record.Field(Table.SelectRows(nameTable, each [RecID] = key){0}, "Team Name"), type text}}),
    #"Grouped Rows" = Table.Group(#"Replace Value", {"Attribute"}, {{"AllRows", each _, type table [Attribute=text, Value=number]}}),
    #"Add Index" = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.AddIndexColumn(_, "Index", 1, 1), type table [Attribute=text, Value=text, Index=Int64.Type]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Add Index", "AllRows", {"Value", "Index"}, {"Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

[okombol]

Thank you, this works! I might need to change it a bit to only adjust the specific columns I need changed, but it does work correctly