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 ...
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"
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...
Is there another way to do this without converting to numbers? My real data's recIds contain numbers and letters
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"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