Forum Discussion

shelly_sethi's avatar
shelly_sethi
Frequent Visitor
4 years ago
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...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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's avatar
shelly_sethi
Frequent Visitor
4 years ago

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's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

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

    • shelly_sethi's avatar
      shelly_sethi
      Frequent Visitor
      4 years ago

      It is not working,please share few more ways.

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

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