Forum Discussion
Format JSON into a table Power BI
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" |
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"
14 Replies
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.
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?
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.
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.
