Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have a table of data I'm pulling into power BI that has a JSON array string in a column for all my rows. For example the string is in this format for each row but with different values and the number of items can go up to 100, so not limited to 2.
{"1":{"animal_quantity":"11","animal_type":"cat","color":"black","size":"Small"},"2":{"animal_quantity":"5","animal_type":"dog","color":"white","size":"large"}}
I'm trying to create a Measure in Power BI or a query that will add the quantity for each animal to a column for each animal. For example I would like Power BI to search the JSON string for "cat", then in a cat column for this record add "11". Then check the record for "dog" and in a dog column add 5.
Is there a way to do this in Power BI with DAX or somehow in Power BI?
I don't want to manually transform to JSON by hand and expand each record because there can be up to 60 different animal types. I'm looking to code it instead.
Thanks
Solved! Go to Solution.
Hi @sherbert99
Here is a sample file with the solution https://we.tl/t-XAVrlt7DhM
Unfortunately, I cannot upload screenshots as I already reached the maximum allowable limit. However, this measure will give you the total for each animal as well as the grand total
# Animals =
SUMX (
VALUES ( Animals[Animal] ),
CALCULATE (
VAR SelectedAnimal = SELECTEDVALUE ( Animals[Animal] )
RETURN
SUMX (
ADDCOLUMNS (
Jason,
"@NumAnimals",
VAR String = Jason[String]
VAR V1 = SEARCH ( SelectedAnimal, String, 1 )
VAR V2 = SEARCH ( "animal_quantity", String, V1 - 40 )
VAR V3 = SEARCH ( "animal_type", String, V1 - 20 )
VAR V4 = MID ( String, V2 + 18, V3 - V2 - 21 )
RETURN
VALUE ( V4 )
),
[@NumAnimals]
)
)
)
Hi @sherbert99
Here is a sample file with the solution https://we.tl/t-XAVrlt7DhM
Unfortunately, I cannot upload screenshots as I already reached the maximum allowable limit. However, this measure will give you the total for each animal as well as the grand total
# Animals =
SUMX (
VALUES ( Animals[Animal] ),
CALCULATE (
VAR SelectedAnimal = SELECTEDVALUE ( Animals[Animal] )
RETURN
SUMX (
ADDCOLUMNS (
Jason,
"@NumAnimals",
VAR String = Jason[String]
VAR V1 = SEARCH ( SelectedAnimal, String, 1 )
VAR V2 = SEARCH ( "animal_quantity", String, V1 - 40 )
VAR V3 = SEARCH ( "animal_type", String, V1 - 20 )
VAR V4 = MID ( String, V2 + 18, V3 - V2 - 21 )
RETURN
VALUE ( V4 )
),
[@NumAnimals]
)
)
)
Hi tamerj1,
Thanks so much I tested that through and it works well. I didn't realize it required counting characters like that but it seems to work OK even if the quantity values change from singel to double digits. Thanks for your help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |