Parse JSON with Power Query in Excel in the same way FetchJson does in Google sheets-CodePudding

I want to pare JSONs in Excel using Power Query that have the following structure

Here the link:

Connection data

Google sheet with data https://docs.google.com/spreadsheets/d/1yO3gX61MroPrYL2l1YnfDxloa5naAX6HG2Q2f5ED_lY/edit?usp=sharing

CodePudding user response：

Try

let  Source =Json.Document(File.Contents("C:\Temp\kumu_json.json")),
Expanded=Table.FromRecords (Source[elements] ),
#"Added Index" = Table.AddIndexColumn(Expanded, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Combine items in list" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", each try if Value.Is(_, type list ) then Text.Combine(_,", ") else _ otherwise _, type text}}),
#"Pivoted Column" = Table.Pivot(#"Combine items in list", List.Distinct(#"Combine items in list"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",List.Transform(Table.ColumnNames(#"Removed Columns"),each {_,type text}))
in  #"Changed Type"