Transpose rows into headers and merge with second query

[ludax]

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?

[Ashish_Mathur]

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"

[Anonymous · Accepted 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

[ludax]

This worked. Once I've seen the way it seemed easy as it always does 🙂 Thanks.