Forum Discussion
Exporting data from website and all headers are coming as columns,I need them header
I have a data where in people are filling some forms on website and I am exporting that data in Power BI.
Problem is data is coming in below format.I want name,email,phone ans state as headers and their corresponding data as rows under them.Please help.
col1 col2
Name xyz
phone 374627
state eerfer
email erferf
Name wferf
phone 45454
state rferf
email erferf
Thanks
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC7DoMgFIDhd2F2ELlU965NunQyDihQSUSNGGrfvieYhl4wsJB8gZ9T10ibxa2jsAplCHNWYlJinHNSwbnbvN9goSarUTfZWYzPBJPaa78jZYUZEsRJ6TS4gOzUmkHN/TSmHqWEcU4ZZ8G6VawpdVZDb4L4/8CJFgWtir3s/ehvfkQPrRbYif5o9L3VspWH+VFCPrTzRHw0t+tBOSeEVHkeBgbpXibTP5WIP/xujwgmD1cdpkfIKMw9NfZILlDevAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"col2"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each _ / 5, type number}}), #"Rounded Up" = Table.TransformColumns(#"Divided Column",{{"Index", Number.RoundUp, Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[col1]), "col1", "col3"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns1"
8 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUaqorFKK1YlWKsjIzwPxjc1NzIzMwULFJYklIKHU1KK01CKwUGpuYmYOSAgkkgYWgppTDheAmWRiCoQoBiE0oZsTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Name] <> "Name")) in #"Filtered Rows"No luck Name and its value XYZ promoted to header and col1 and col2 removed,rest all below values are same.But the second name also removed from data.
If you want to retain 2nd and onwards names, just delete the Filter step.
Thanks a ton...it is working
