Extract/parse JSON code in Power BI

4 april 2024 Oprindeligt udgivet på alexanderhenkel.dk

Learn how to extract JSON code into single columns in Power BI

The challenge

If you are provided with a column containing JSON code like the picture below, you want to split up the code into single columns, to be able to work with the data in Power BI

Power BI table column containing raw JSON code strings

I have seen some scenarios where peoples first choice is to use the split column -> delimiter function, and then splitting by ,

Power Query editor showing the Split Column by Delimiter option with comma selected

Don’t to that, because you will end up with columns that looks like this

Power BI columns resulting from splitting by comma, showing messy fragmented data instead of clean separate fields

I am sure what you want them to look like is this

Power BI table with JSON data correctly split into clean separate named columns

The solution

In the Power Query editor there is a function available under transform -> JSON

Power Query editor Transform menu with the JSON option highlighted

After you have transformed your data, your column will now look like this

Power Query column showing Record entries after applying the JSON transform

Click on the arrow and select all the columns that are relevant. If there are data/columns that you don’t need, leave them out (you can always add them later)

Also unselect Use original column as prefix

Power Query expand column dialog with relevant fields selected and Use original column name as prefix unchecked

Now your JSON data will be split into separate columns, and you can continue working on your model

Power BI table with JSON data correctly split into clean separate named columns

‎