convert REST API - json output into table Power BI

Question

After conversion of json output into table in Power BI.

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

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

Thanks for the help

Answer 1

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"