Forum Discussion
Solved
Extract string from json object/array when using DirectQuery mode
Hi, How can I extract a string from this json, when using DirectQuery mode. I either have 1 object {"label":"a label","value":"a value"} and would like to pull out the value. or an arra...
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
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,
Dale
You have saved me hours, thank you very much