Forum Discussion
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 array
[{"label":"1st label","value":"1st value"},{"label":"2nd label","value":"2nd value"}]
and would like to pull out all values
e.g. "1st value, 2nd value"
Thank you
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
4 Replies
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,
DaleThanks 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
