cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twister8889
Helper V
Helper V

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

IDAttributeValueExpectedIDTable1

StringtoID1

CustomerCustomer11
StringtoID1ProductPrduct11
StringtoID2CustomerCustomer22
StringtoID2ProductProduct22

 

Table2

IDTable1ValueExpectedIDTable1
StringtoID12001
StringtoID23002

 

I need to join Table1 and Table2, but I would like to create a column as number index to replace the "StringtoID" values. How do I do the ExpectedIDTable1 on Table1 and Table2?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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"

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi  @twister8889 ,

 

If all the ID columns are as you show,you could also use below M codes:

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 Custom" = Table.AddColumn(#"Changed Type", "Custom", each [ID]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","StringtoID"," ",Replacer.ReplaceText,{"Custom"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Custom", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

And you will see:

vkellymsft_0-1629784470978.png

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

 

Greg_Deckler
Super User
Super User

@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"

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.