Loading a json file into Power Query
I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can't figure out how to get Power Query to convert the JSON file to a tabular format.
Is there a way to convert JSON to a table in Power Query without writing a custom query?
microsoft-excel json power-query
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
add a comment |
I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can't figure out how to get Power Query to convert the JSON file to a tabular format.
Is there a way to convert JSON to a table in Power Query without writing a custom query?
microsoft-excel json power-query
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
add a comment |
I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can't figure out how to get Power Query to convert the JSON file to a tabular format.
Is there a way to convert JSON to a table in Power Query without writing a custom query?
microsoft-excel json power-query
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can't figure out how to get Power Query to convert the JSON file to a tabular format.
Is there a way to convert JSON to a table in Power Query without writing a custom query?
microsoft-excel json power-query
microsoft-excel json power-query
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
asked Sep 23 '15 at 18:46
CountZeroCountZero
38114
38114
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.
Here's a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json
After running the PQ "From Web", the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.
This returns a single row, with the second column (Value) containing a List. Click the expand button next to "Value" to expand the List.
This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.
Note there is more depth to this particular JSON layout - see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.
Here's the full code to get to a table - each step was generated by clicking on the UI.
let
Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value",
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
#"Expanded Value1"
edited Jan 11 at 12:25
hellow
1035
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
|
show 1 more comment
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f977249%2floading-a-json-file-into-power-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.
Here's a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json
After running the PQ "From Web", the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.
This returns a single row, with the second column (Value) containing a List. Click the expand button next to "Value" to expand the List.
This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.
Note there is more depth to this particular JSON layout - see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.
Here's the full code to get to a table - each step was generated by clicking on the UI.
let
Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value",
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
#"Expanded Value1"
edited Jan 11 at 12:25
hellow
1035
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
|
show 1 more comment
JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.
Here's a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json
After running the PQ "From Web", the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.
This returns a single row, with the second column (Value) containing a List. Click the expand button next to "Value" to expand the List.
This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.
Note there is more depth to this particular JSON layout - see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.
Here's the full code to get to a table - each step was generated by clicking on the UI.
let
Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value",
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
#"Expanded Value1"
edited Jan 11 at 12:25
hellow
1035
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
|
show 1 more comment
JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.
Here's a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json
After running the PQ "From Web", the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.
This returns a single row, with the second column (Value) containing a List. Click the expand button next to "Value" to expand the List.
This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.
Note there is more depth to this particular JSON layout - see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.
Here's the full code to get to a table - each step was generated by clicking on the UI.
let
Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value",
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
#"Expanded Value1"
edited Jan 11 at 12:25
hellow
1035
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
JSON data often appears as Records within Lists within Records (and other variations). You can usually use the PQ UI to expand those objects without writing code.
Here's a sample JSON endpoint: http://api.nobelprize.org/v1/laureate.json
After running the PQ "From Web", the next step is to click the Record Tools / Convert / Into Table button in the PQ ribbon.
This returns a single row, with the second column (Value) containing a List. Click the expand button next to "Value" to expand the List.
This returns multiple rows, with each second column (Value) now containing a Record. Expand the record to get the columns of data in a tabular format.
Note there is more depth to this particular JSON layout - see the Value.prizes column at the far right. Your exact steps may vary, depending on your JSON input and your output requirements.
Here's the full code to get to a table - each step was generated by clicking on the UI.
let
Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value",
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"},
{"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode",
"bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
#"Expanded Value1"
edited Jan 11 at 12:25
hellow
1035
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
edited Jan 11 at 12:25
hellow
1035
edited Jan 11 at 12:25
hellow
1035
edited Jan 11 at 12:25
hellow
1035
1035
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
answered Sep 24 '15 at 1:07
Mike HoneyMike Honey
1,7561511
1,7561511
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
|
show 1 more comment
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
Thanks, Mike. Works really well. I'm trying to adapt it to a really complex JSON file I downloaded. I think this will get me 90% of the way there.
– CountZero
Sep 24 '15 at 1:37
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
You're welcome. W Gibson fan?
– Mike Honey
Sep 24 '15 at 3:21
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Sure am. Was it that transparent?
– CountZero
Sep 24 '15 at 3:27
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
Only to those who took the red pill ...
– Mike Honey
Sep 24 '15 at 3:34
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
PS: I've since managed to clone Chris Webb's ExpandAll function to one that works on Record-type columns - expanding every column as far as it will go. Check out: gist.github.com/Mike-Honey/0a252edf66c3c486b69b
– Mike Honey
Jun 26 '16 at 23:13
|
show 1 more comment
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f977249%2floading-a-json-file-into-power-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
