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"
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
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)
sorry. As Anonymous said you need to lose the "Promote headers" step.
Ok, I did remove the "Promote headers" and now I am getting this error
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"