Forum Discussion
okombol
11 months agoRegular Visitor
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 ...
- 11 months ago
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"
Omid_Motamedise
11 months agoSuper User
hi okombol
Does the length of RecID fixed for all the items?
okombol
11 months agoRegular Visitor
Yes, they are all 32 character mixtures of numbers and capital letters