Search for JSON values from JSON array

[sherbert99]

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

[tamerj1 · Accepted 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]
        )
    )
)

[sherbert]

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!