jsonpowerbipowerquerypowerbi-rest-api

convert REST API - json output into table Power BI

After conversion of json output into table in Power BI. enter image description here

However, I'm unable to transform above format into below format in Power BI.

expected result

Any suggestion how I can solve this problem in Power BI.

Thanks for the help

Solution

  • You can try this code

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [column.lable]="name" then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[column.lable]), "column.lable", "column.value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in  #"Removed Columns1"

    enter image description here