Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | Customer1 | 1 |
StringtoID1 | Product | Prduct1 | 1 |
StringtoID2 | Customer | Customer2 | 2 |
StringtoID2 | Product | Product2 | 2 |
Table2
IDTable1 | Value | ExpectedIDTable1 |
StringtoID1 | 200 | 1 |
StringtoID2 | 300 | 2 |
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?
Solved! Go to Solution.
@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"
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:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |