Forum Discussion
Solved
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 an...
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"
API output
| col1 | col2 | col3 |
| firstname | 1658138110639 | cxvvxvxv |
| company | 1658138110639 | dfvfvv |
| 1658138110639 | sddsfdfv | |
| mobilephone | 1658138110639 | 435664565 |
| state | 1658138110639 | Delhi |
| firstname | 1658137422492 | dfvdfv |
| company | 1658137422492 | wferfer |
| 1658137422492 | fgbfdbd | |
| mobilephone | 1658137422492 | 4355656 |
| state | 1658137422492 | UP |
| firstname | 1658136333900 | sdfvdvd |
| company | 1658136333900 | sdafvdfv |
| 1658136333900 | dsfvdv | |
| mobilephone | 1658136333900 | 546455 |
| state | 1658136333900 | MP |
Format I am looking for
| firstname | company | mobilephone | state | |
| cxvvxvxv | dfvfvv | sddsfdfv | 435664565 | Delhi |
| dfvdfv | wferfer | fgbfdbd | 4355656 | UP |
| sdfvdvd | sdafvdfv | dsfvdv | 546455 | MP |
Hope this helps now.
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalSee 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"