Forum Discussion

nadavsnn's avatar
nadavsnn
Frequent Visitor
2 years ago

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