Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
Dale
You have saved me hours, thank you very much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |