Forum Discussion

ludax's avatar
ludax
New Member
8 years ago
Solved

Transpose rows into headers and merge with second query

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?

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      ludax
      New Member
      8 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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"