Split column with multiple headers and rows in it

Question

Hi,

I have the following file which you can find attached.
Please know that due to security reasons this is a sample file and the data is slightly changed.
The original file contains more than 40 000 rows.
The file will be changed with new data regularly so the attached rows are an example ones.
This is a Power BI dataset.

The task is to split the Audit column into different columns.
Firstly, you can find the header and then the values.

For example:

"Id":"fff46-df4d-8hd-wer5-8dh8"
"CapacityName":"VGHO JKOP 5H"

They are split by commas but if there is a missing header and value there is no indication for it - there is no empty space between 2 commas or something like that.

And this is the issue exactly - for the null values there are no records at all, therefore, the number of headers and their values for each Audit row is different and there is no empty value/cell for the missing ones.

I am not sure how to split them properly and create a sustainable solution for this case.

I will be glad if somebody can assist me further.

Thank you in advance!

Sample.xlsx

TheOC · Accepted Answer

hi @YoannaRaykova

I believe what you need is the json parse tool. This seems to work perfectly:

I've produced two example outputs, one with them split into rows, and then one crosstabbed to make it as clean as possible.

Hope this helps, please find attached.
TheOC