Forum Discussion

kevco's avatar
kevco
Frequent Visitor
7 years ago
Solved

Pivot name value pairs

I imported XML into two columns that now look like so in Power BI:

 

 

id                 47847
first              Joe
last              Smith
loc               333 south st
id                 4564
first              sue
last              jones
loc               3939 North blvd
id                220
first             ellie
last              able
loc              22 west st

I would like it to look like 

 

 

id                         first                    last                      loc

47847                  Joe                      Smith                 333 South St.

4564                    Sue                  etc......

 

I suspect there is a pivot in there somewhere, but when I try to pivot, I'm getting errors, or it doesn't look right.

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    This M code works

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsssKi5R0oHQhkqxOtFKOYlgERAFFchPBvHzkyHczBQgLzMFwkHRb4Su3whVvxGyfiNM/cbo+o1R9Rsj6wdyYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
        Partition = Table.Group(#"Changed Type", {"Column1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
        #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Column2", "Index"}, {"Column2", "Index"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Column1]), "Column1", "Column2"),
        #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Index", "id", "first", "last", "loc"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}, {"first", type text}, {"last", type text}, {"loc", type text}})
    in
        #"Changed Type1"

     

    Hope this helps.

3 Replies

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

    Hi,

     

    This M code works

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsssKi5R0oHQhkqxOtFKOYlgERAFFchPBvHzkyHczBQgLzMFwkHRb4Su3whVvxGyfiNM/cbo+o1R9Rsj6wdyYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
        Partition = Table.Group(#"Changed Type", {"Column1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
        #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Column2", "Index"}, {"Column2", "Index"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Column1]), "Column1", "Column2"),
        #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Index", "id", "first", "last", "loc"}),
        #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}, {"first", type text}, {"last", type text}, {"loc", type text}})
    in
        #"Changed Type1"

     

    Hope this helps.