Forum Discussion

okombol's avatar
okombol
Regular Visitor
11 months ago
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 ...
Need Help
  • jgeddes's avatar
    jgeddes
    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"
jgeddes's avatar
jgeddes
Super User
11 months ago

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's avatar
    okombol
    Regular Visitor
    11 months ago

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

    • jgeddes's avatar
      jgeddes
      Super User
      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"
      • okombol's avatar
        okombol
        Regular Visitor
        11 months ago

        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