Forum Discussion

twister8889's avatar
twister8889
Helper V
5 years ago
Solved

Creating index by group column power query

Hi guys,   I need to join two tables by one field, and I would like to do this using one number field, not string. Table1 ID Attribute Value ExpectedIDTable1 StringtoID1 Customer Cus...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    twister8889 Perhaps:

    Table (1)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pysxLL8n3dDFU0lFyLi0uyc9NLUJiGirF6qArCyjKTylNLgGzQAx0NUbYjTLCogzZKDALqCgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Attribute", type text}, {"Value", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"ID"}, {{"Min", each List.Min([Index]), type number}, {"Table", each _, type table [ID=nullable text, Attribute=nullable text, Value=nullable text, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Table" = Table.ExpandTableColumn(#"Added Index1", "Table", {"Attribute", "Value"}, {"Table.Attribute", "Table.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Min"})
in
    #"Removed Columns"

     

    Table (2)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pysxLL8n3dDFU0lEyMjBQitVBFjUCihqDRGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDTable1 = _t, Value = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
in
    #"Added Index"

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

twister8889 Perhaps:

Table (1)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pysxLL8n3dDFU0lFyLi0uyc9NLUJiGirF6qArCyjKTylNLgGzQAx0NUbYjTLCogzZKDALqCgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Attribute", type text}, {"Value", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"ID"}, {{"Min", each List.Min([Index]), type number}, {"Table", each _, type table [ID=nullable text, Attribute=nullable text, Value=nullable text, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Table" = Table.ExpandTableColumn(#"Added Index1", "Table", {"Attribute", "Value"}, {"Table.Attribute", "Table.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Min"})
in
    #"Removed Columns"

 

Table (2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pysxLL8n3dDFU0lEyMjBQitVBFjUCihqDRGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDTable1 = _t, Value = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
in
    #"Added Index"