Search code examples
jsonexcelpowerbipowerbi-datasource

PowerBI - Excel datasource contains JSON in column

I have an excel sheet that has three columns A, B and C.

A and B contain regular text. A firstname and lastname, if you will. The third column C contains JSON data.

Is there a way I can read this file into PowerBI and have it automatically parse out the JSON data into additional columns? In PowerBI Desktop Client, I can use an excel sheet as the datasource, and it loads in my data into the client, however it naturally treats column C as just text. I've had a look at the Advanced Editor and I'm thinking I might have to include something in there to help parse that out.

Any ideas?

Solution

  • I figured it out. In the query editor, right-click on the column that contains the JSON, go to Transform and select JSON. It will parse out the data, allowing you to add them in as additional column.

    enter image description here

    Extremely handy!