Forum Discussion
Solved
Format JSON into a table Power BI
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 usi...
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"
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
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.
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 SourceThis 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"": ["
]
},
}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