Pivot name value pairs

[kevco]

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 · Accepted solution]

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.

[kevco]

This helps a lot! Thanks Ashish_Mathur!

[Ashish_Mathur]

You are welcome.