Forum Discussion
Extract string from json object/array when using DirectQuery mode
Hi jamienourish,
If all the strings have the same format, please try out the formulas below as calculated columns. The functions we can use are limited due to the limitation of Direct Query.
FirstValue = VAR startNum = FIND ( """value"":", [json_filed], 1, 9999 ) + 9 VAR chars = FIND ( """}", [json_filed], 1, 9999 ) - startNum RETURN MID ( [json_filed], startNum, chars )
SecondValue = VAR firstPosition = FIND ( """value"":", [json_filed], 1, 9999 ) + 9 VAR secondPosition = FIND ( """value"":", [json_filed], firstPosition, 9999 ) + 9 VAR chars = FIND ( """}]", [json_filed], 1, 9999 ) - secondPosition RETURN IF ( FIND ( "[", [json_filed], 1, 9999 ) <> 9999, MID ( [json_filed], secondPosition, chars ), BLANK () )
Best Regards,
Dale
Hi jamienourish,
Could you please provide more details?
What's the data source?
Where does the JSON object store?
Do the JSON objects have the same length and the same format?
Best Regards,
Dale
Thanks for replying,
Yes the database has a generic column that is a string, and power bi is connected to that using directquery.
in the column I store json that looks like above.
I just need to extract the value from that string
Hi jamienourish,
If all the strings have the same format, please try out the formulas below as calculated columns. The functions we can use are limited due to the limitation of Direct Query.
FirstValue = VAR startNum = FIND ( """value"":", [json_filed], 1, 9999 ) + 9 VAR chars = FIND ( """}", [json_filed], 1, 9999 ) - startNum RETURN MID ( [json_filed], startNum, chars )
SecondValue = VAR firstPosition = FIND ( """value"":", [json_filed], 1, 9999 ) + 9 VAR secondPosition = FIND ( """value"":", [json_filed], firstPosition, 9999 ) + 9 VAR chars = FIND ( """}]", [json_filed], 1, 9999 ) - secondPosition RETURN IF ( FIND ( "[", [json_filed], 1, 9999 ) <> 9999, MID ( [json_filed], secondPosition, chars ), BLANK () )
Best Regards,
DaleYou have saved me hours, thank you very much
