Forum Discussion
JSON Parsing - Each Value as T/F Column
Long story short, my team recently migrated from a legacy system to a newer system for operations management (think like Crsytal Reports to BigQuery). The new system stores characteristics as a JSON object rather than as individual columns with 1s and 0s.
Ex.
column: features
value: ["Lettuce", "Pickles", "Ketchup"]
vs
Lettuce: 1
Pickles: 1
Tomatoes: 0
Ketchup: 1
Mustard: 0
I want to get the values out of the JSON object and set the data up in the same way as the original system, so I can evaluate how many sandwiches with pickles each store sells (an anonymized example). I can't get the parsing to work in the transformations and Claude/ChatGPT can't figure it out either.
Anything I can do in Power Bi? I'd rather not have to put the file in Jupyter and try to work with Python 😕
Hi marketanalystlb ,
Thank you for posting your query in the Microsoft Fabric Community Forum.
I reproduced this scenario using sample data and was able to achieve it directly in Microsoft Power BI Desktop using Power Query:
First, parse the JSON column so it is recognized as a list instead of text. Then expand the list into rows so each feature appears on its own row. Next, add a custom column with a constant value (such as 1) to indicate the presence of each feature. After that, pivot the feature column using this new column as the values and set the aggregation to Sum, which will create separate columns for each feature. Finally, replace any null values with 0 to represent missing features. This converts the JSON array into a 1/0 column structure similar to your previous system and allows you to easily analyze scenarios like counting how many records include a specific feature.Thank you .
5 Replies
Have you tried the JSON connector in Power BI Desktop? Didn't it have the expect result?
Hey marketanalystlb ,
could you upload a sample json? I can parse it for you
Hi marketanalystlb ,
Thank you for posting your query in the Microsoft Fabric Community Forum.
I reproduced this scenario using sample data and was able to achieve it directly in Microsoft Power BI Desktop using Power Query:
First, parse the JSON column so it is recognized as a list instead of text. Then expand the list into rows so each feature appears on its own row. Next, add a custom column with a constant value (such as 1) to indicate the presence of each feature. After that, pivot the feature column using this new column as the values and set the aggregation to Sum, which will create separate columns for each feature. Finally, replace any null values with 0 to represent missing features. This converts the JSON array into a 1/0 column structure similar to your previous system and allows you to easily analyze scenarios like counting how many records include a specific feature.Thank you .
Hi marketanalystlb ,
Could you please confirm if your issue has been resolved using the suggested approach? This will help other community members facing similar scenarios.
Thank you for being part of the Microsoft Fabric Community.
Hi marketanalystlb ,
Just checking in again on your issue. Could you please confirm if the solution worked?
Please let us know if you need any further assistance.
Thank you.
