Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.