Exporting data from website and all headers are coming as columns,I need them header

[shelly_sethi]

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

[Vijay_A_Verma]

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"

[shelly_sethi]

Thanks a ton...it is working

[shelly_sethi]

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.

[Vijay_A_Verma]

If you want to retain 2nd and onwards names, just delete the Filter step.

[shelly_sethi]

It is not working,please share few more ways.

[Vijay_A_Verma]

Can you post the result expected for the input table given? You can make the output in Excel and post the screenshot here. 

[shelly_sethi]

API output

col1	col2	col3
firstname	1658138110639	cxvvxvxv
company	1658138110639	dfvfvv
email	1658138110639	sddsfdfv
mobilephone	1658138110639	435664565
state	1658138110639	Delhi
firstname	1658137422492	dfvdfv
company	1658137422492	wferfer
email	1658137422492	fgbfdbd
mobilephone	1658137422492	4355656
state	1658137422492	UP
firstname	1658136333900	sdfvdvd
company	1658136333900	sdafvdfv
email	1658136333900	dsfvdv
mobilephone	1658136333900	546455
state	1658136333900	MP

Format I am looking for

firstname	company	email	mobilephone	state
cxvvxvxv	dfvfvv	sddsfdfv	435664565	Delhi
dfvdfv	wferfer	fgbfdbd	4355656	UP
sdfvdvd	sdafvdfv	dsfvdv	546455	MP

Hope this helps now.

[Vijay_A_Verma · Accepted solution]

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"