Power query - parse all JSON column

[nadavsnn]

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 

[nadavsnn]

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 

[foodd]

You can upload the PBIX to a cloud service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share the URL.

[nadavsnn]

https://1drv.ms/u/s!Ak6th8HEcTdLj4wEwKEFd5m1rExjsQ?e=bQxxzu 

thanks!

[v-cgao-msft]

Hi @nadavsnn ,

There seems to be a problem with this part of the code:

Please try:

if Value.Is(value, type record) then

Value.Is - PowerQuery M | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[nadavsnn]

thanks, Gao

I still get this error: 

Expression.Error: The name 'expandJsonObject' wasn't recognized. Make sure it's spelled correctly.

in the advanced editor everything is ok. after I close it I get an error

 

 

[foodd]

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-...

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/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.