PowerBI - Excel datasource contains JSON in column

0
votes

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?

1
jsonexcelpowerbipowerbi-datasource
In the header do you see a couple of arrows in json column? If yes, click it. – eshwar
No arrows, however I was able to right-click the column header and select "Transform -> JSON" and it worked. – Ray Hogan

1 Answers

1
votes

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!