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,
not sure if the description is correct but this is what I was trying to do so far. I need to merge table 2 to table 1, so each unique attribute is added as new column and value populated. List of attributes can change so would prefer if this is somehow dynamic. Each item has same set of attributes. Can it be done preferably in query editor and not on the report?
Solved! Go to Solution.
@ludax,
You can pivot columns in Table2, then merge Table2 and Table1 based on the itemcode. Add blank queries in Power BI Desktop, then paste the following codes into Advanced Editor of the blank queries to test the process.
Table1:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzTVU0lEKS8wpTYUzTJRidSByRjAhOMMULmeMIWemFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ItemCode = _t, #"Property 1" = _t, #"Property 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemCode", type text}, {"Property 1", type text}, {"Property 2", type text}}) in #"Changed Type"
Table2:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzTVU0lFyLCkpAtFhiTmlqYZKsTqoUkYwKSMMKWOYlDFcygjdQBMMKbiBphhScAPN4FLG6AaaY0jBDbTAkIIbaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ItemCode = _t, AttributeName = _t, AttributeValue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemCode", type text}, {"AttributeName", type text}, {"AttributeValue", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[AttributeName]), "AttributeName", "AttributeValue") in #"Pivoted Column"
Merge1:
let Source = Table.NestedJoin(Table1,{"ItemCode"},Table2,{"ItemCode"},"Table2",JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Attr1", "Attr2", "Attr3", "ItemCode"}, {"Table2.Attr1", "Table2.Attr2", "Table2.Attr3", "Table2.ItemCode"}) in #"Expanded Table2"
Regards,
Lydia
Hi,
Here's the M code i used
let
Source = Table.NestedJoin(Table1,{"ItemCode"},Table2,{"ItemCode"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"AttributeName", "AttributeValue"}, {"AttributeName", "AttributeValue"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Table2", List.Distinct(#"Expanded Table2"[AttributeName]), "AttributeName", "AttributeValue")
in
#"Pivoted Column"
@ludax,
You can pivot columns in Table2, then merge Table2 and Table1 based on the itemcode. Add blank queries in Power BI Desktop, then paste the following codes into Advanced Editor of the blank queries to test the process.
Table1:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzTVU0lEKS8wpTYUzTJRidSByRjAhOMMULmeMIWemFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ItemCode = _t, #"Property 1" = _t, #"Property 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemCode", type text}, {"Property 1", type text}, {"Property 2", type text}}) in #"Changed Type"
Table2:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzTVU0lFyLCkpAtFhiTmlqYZKsTqoUkYwKSMMKWOYlDFcygjdQBMMKbiBphhScAPN4FLG6AaaY0jBDbTAkIIbaKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ItemCode = _t, AttributeName = _t, AttributeValue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ItemCode", type text}, {"AttributeName", type text}, {"AttributeValue", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[AttributeName]), "AttributeName", "AttributeValue") in #"Pivoted Column"
Merge1:
let Source = Table.NestedJoin(Table1,{"ItemCode"},Table2,{"ItemCode"},"Table2",JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Attr1", "Attr2", "Attr3", "ItemCode"}, {"Table2.Attr1", "Table2.Attr2", "Table2.Attr3", "Table2.ItemCode"}) in #"Expanded Table2"
Regards,
Lydia
This worked. Once I've seen the way it seemed easy as it always does 🙂 Thanks.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |