Forum Discussion
Solved
Replacing All Record IDs with Names from a different table
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]
)
)
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"
