Format JSON into a table Power BI

[datatbl123]

Example

Hi,

I have an issue where the first column pulling from the data is displaying as a column header but it should be the first option on line 1. Can anyone help with the current code I am using in Advanced Editor?

let
Source = Json.Document(Web.Contents("https://website.com/customfielditems?customfield_id=72879", [Headers=[Authorization="Key"]])),
results = Source[results],
customfielditems = results[customfielditems],
#"Converted to Table" = Record.ToTable(customfielditems),
modtable = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),
#"Expanded Column2" = Table.ExpandRecordColumn(modtable, "Column2",
{"id", "name"},
{"id", "name"})
in
#"Expanded Column2"

[Daryl-Lynch-Bzy]

Hi @datatbl123 ,  do you need the following step?

modtable = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),

It will promote the first Record to the heading, so the "Name" is not extracted. 

[lbendlin]

What do you want the column header name to be instead? is that number 4308715 part of the data that you want to keep?

Post a sample JSON and indicate the expected outcome.

[datatbl123]

I am not sure what that first column is but it seems to be a list of the id # (just like in column 2) and yes to respond to your question, 4308715 is part of the data and should be the first entity in the data list showing up first in column 2, not sure why it is creating a column header with it in a column 1. This is ultimately what I would like the output to be, just simple, 1 column id, 1 column names. I posted the JSON code in my first post, isn't it showing up?

[lbendlin]

No, the code you posted is the Power Query code.  I wanted to see what your raw data JSON looks like.  By its very nature XML and JSON are hierarchical data types, and converting them into a table will result in some information loss if you are not careful.

[datatbl123]

Oh I see, I am not sure what the raw data JSON looks like, I am just trying to pull data from Quickbooks Time into Power BI.

[lbendlin]

Run this and then copy/paste the output (sanitize as needed)

let
Source = Json.Document(Web.Contents("https://website.com/customfielditems?customfield_id=72879", [Headers=[Authorization="Key"]])),
results = Source[results],
customfielditems = results[customfielditems]
in
customfielditems

[datatbl123]

This is what I get when I copy/paste the code above, how can I get the id and name column header and the information showing underneath it? Thank you for your help.

[lbendlin]

Well, we may have to brute force it.  Run this code and post the results (make sure to sanitize)

let
Source = Web.Contents("https://website.com/customfielditems?customfield_id=72879", [Headers=[Authorization="Key"]])
in
Source

[datatbl123]

This is what I got.

This is what is the JSON

#Column1
{
" ""results"": {"
" ""customfielditems"": {"
" ""4308715"": {"
" ""id"": 4308715,"
" ""customfield_id"": 72879,"
" ""active"": true,"
" ""short_code"": """","
" ""name"": ""Accounting Services:A"","
" ""last_modified"": ""2020-11-25T18:31:47+00:00"","
" ""required_customfields"": ["

]
},
" ""4097548"": {"
" ""id"": 4097548,"
" ""customfield_id"": 72879,"
" ""active"": true,"
" ""short_code"": """","
" ""name"": ""Accounting Services:B"","
" ""last_modified"": ""2020-09-01T23:04:24+00:00"","
" ""required_customfields"": ["

]
},
" ""1463163"": {"
" ""id"": 1463163,"
" ""customfield_id"": 72879,"
" ""active"": true,"
" ""short_code"": """","
" ""name"": ""C"","
" ""last_modified"": ""2019-11-23T01:06:51+00:00"","
" ""required_customfields"": ["

]
},
}

[lbendlin]

Ah, ok. As you said it's just a repetition of the ID field. In that case you should be good with 

let
Source = Json.Document(Web.Contents("https://website.com/customfielditems?customfield_id=72879", [Headers=[Authorization="Key"]])),
results = Source[results],
customfielditems = results[customfielditems],
#"Converted to Table" = Record.ToTable(customfielditems),
modtable = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),
#"Expanded Column2" = Table.ExpandRecordColumn(modtable, "Column2",
{"id", "name"},
{"id", "name"}),
RemovedOthers=Table.SelectColumns(#"Expanded Column2",{"id", "name"})
in
RemovedOthers

[datatbl123]

For some reason, that is still making the first data point to be removed, it did remove the first column though which was unecessary so that's good, do you know why it would remove the first line of data? (still missing id# 4308715)

[lbendlin]

sorry. As @Daryl-Lynch-Bzy said you need to lose the "Promote headers"  step.

[datatbl123]

Ok, I did remove the "Promote headers" and now I am getting this error

[lbendlin · Accepted solution]

let
Source = Json.Document(Web.Contents("https://website.com/customfielditems?customfield_id=72879", [Headers=[Authorization="Key"]])),
customfielditems = Source[results][customfielditems],
    #"Converted to Table" = Record.ToTable(customfielditems),
    #"Removed Other Columns" = Table.SelectColumns(#"Converted to Table",{"Value"}),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Removed Other Columns", "Value", {"id", "name"}, {"id", "name"})
in
    #"Expanded Value"