Import columnar json in Excel using PowerQuery

Question

How would I import the following json

{
    "Column1": ["r1c1", "r2c1", "r3c1"],
    "Column2": ["r1c2", "r2c2", "r3c2"],
    "Column3": ["r1c3", "r2c3", "r3c3"]
}

using PowerQuery and get the table in Excel:

?

Notes:

I already tried without success Data\Connect to data...

... Into Table...

Thanks

data .. get data .. from file ... from JSON ... then after review file ... close and load — horseyride
– horseyride, Commented Oct 17, 2022 at 11:13
Thanks for the quick reply, I already tried that :-) and a few other mashups without success — ams1
– ams1, Commented Oct 17, 2022 at 11:20
from your image, click the arrow atop the Value column to expand it — horseyride
– horseyride, Commented Oct 17, 2022 at 11:33
@horseyride: the json is NOT the "normal" row style json (aka [{"column1": "r1c1", "column2": "r1c2"}, {"column1": "r2c1", "column2": "r2c2"}...]). It's a "columnar style" json - each key represents a column P.S.: thanks — ams1
– ams1, Commented Oct 17, 2022 at 11:39

horseyride · Accepted Answer · 2022-10-17 11:45:51Z

1

Try

let Source = Record.ToTable(Json.Document(File.Contents("C:\Temp\test.json"))),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.AddIndexColumn(Table.FromList([Value], null, {"contents"}), "Index", 0, 1, Int64.Type)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"contents", "Index"}, {"contents", "Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name]), "Name", "contents"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in  #"Removed Columns1"

answered Oct 17, 2022 at 11:45

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import columnar json in Excel using PowerQuery

Notes:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Notes:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related