Convert Raw data to structure Data

Hi  gnair21 

You can put the following code to Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY67DsIwDEX/JXMHBAuMbRYQjyViSjO4rZUENamUx8Dfk1ggqkpe7rF9dKVkrGF9H+00GQjJAFONZA9wWLjIAV4E+AwxFnKkJHBMdvElt5TNMgwW654bLHeVkfYGXkNYK6/ZfcFPeaJ08QkDxlTAPUc7Emx1fdnv/sJnx9e2s3UzvGF9fNg07DYNhfW6DFPqAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.