Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
nadavsnn
Frequent Visitor

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 6
nadavsnn
Frequent 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 

 

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.

Hi @nadavsnn ,


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

vcgaomsft_0-1720491649669.png

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

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

forum screenshot1.PNG

forum screenshot2.PNG

foodd
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.