Forum Discussion
Power query - parse all JSON column
hi all
I am trying to write an M language function that will extract all fields from a JSON column - the names in the JSON are changing so I cannot just declare the names in the parsing code.
my function get this error: Expression.Error: The name 'expandJsonObject' wasn't recognized. Make sure it's spelled correctly.
// Function to expand nested JSON objects
let
ExpandAllJsonObjects = (table as table, columnName as text) =>
let
// Define a helper function to expand a single JSON value
expandJsonObject = (value as any) =>
if value is record then
// Get all field names and their expanded values
List.Transform(
Record.FieldNames(value),
each expandJsonObject(Record.Field(value, _, MissingField.Ignore))
)
else {value}, // Wrap non-record values in a list
// Get a list of all values in the JSON column
jsonValues = Table.Column(table, columnName),
// Expand each JSON value and combine them into a single list
expandedValues = List.Combine(List.Transform(jsonValues, each expandJsonObject(_))),
// Create a new table with the expanded values
expandedTable = Table.FromList(expandedValues, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
expandedTable
in
ExpandAllJsonObjects
any help will be much appreciated
6 Replies
- fooddCommunity Champion
remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied. - nadavsnnFrequent Visitor
hi, I tried to upload a sample pbix file but the page does not support it. I can add a sample data as table and add the power query code if needed