Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have a column 'Results' with information stored as string (in JSON format) and would like to parse this string and get only necessary information from this column. After reading through support forums, I understood the easier way is to use 'Parse JSON' via transform data and retrieve only the needed info but I can't use it as it breaks Query folding for 'Incremental Refresh'.
[Note: The entire string in 'Results' column in too large and I have only pasted the relevant part]
In order to achieve this, I am trying to create a new column with DAX query as pasted below. I would like to perform the below operations.
Can you have a look at the DAX query and please let me know what am I doing wrong? Also, is there any other better/efficient way to achieve this? Would appreciate your help on this.
Index | Serial Number | Results |
1 | TST2334 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"","MetricUnit":null,"MeasurementUnit":null}], ....................................... |
2 | DY75677 | {"MeasuredValue":[{"Name":"TestType","Value":"NonMeasurement","MetricUnit":null,"MeasurementUnit":null}], .......................... |
3 | DCQ7869 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"264","MetricUnit":null,"MeasurementUnit":""}, ............................. |
4 | UAE9354 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"88.23","MetricUnit":null,"MeasurementUnit":"dB"}, .................... |
5 | F1R3496 | {"MeasuredValue":[{"Name":"TestType","Value":"SingleValueMeasurement","MetricUnit":null,"MeasurementUnit":null}],"InputParameter":[{"Name":"TestFunctionVisibility","Value":"True","MetricUnit":null,"MeasurementUnit":null} |
6 | UAE9377 | {"MeasuredValue":[{"Name":"TestType","Value":"CurveMeasurement","MetricUnit":null,"MeasurementUnit":null},{"Name":"Output","Value":"83.64","MetricUnit":null,"MeasurementUnit":............ |
Output =
VAR FindOutput =
IFERROR ( SEARCH ( "Output", [Results],, 1 ), 1 )
VAR FindValue =
IFERROR ( SEARCH ( "Value", [Results], FindOutput, 1 ), 1 )
VAR FindColon =
IFERROR ( SEARCH ( ":", [Results], FindValue, 1 ), 1 )
VAR FindComma =
IFERROR ( SEARCH ( ",", [Results], FindColon, 1 ), 1 )
VAR temp =
VALUE ( FindComma - FindColon - 3 )
RETURN
IFERROR ( MID ( [Results], VALUE ( FindColon + 2 ), temp ), "null" )
Thanks in advance!
Solved! Go to Solution.
Hi @DreamToGet ,
Try to adjust your measure to below:
Output =
VAR FindOutput =
IFERROR ( SEARCH ( "Output", [Results],, 1 ), 1 )
VAR FindValue =
IFERROR ( SEARCH ( "Value", [Results], FindOutput, 1 ), 1 )
VAR FindColon =
IFERROR ( SEARCH ( ":", [Results], FindValue, 1 ), 1 )
VAR FindComma =
IFERROR ( SEARCH ( ",", [Results], FindColon, 1 ), 1 )
VAR temp =
VALUE ( FindComma - FindColon - 3 )
RETURN
IF (
IFERROR ( INT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ) ), BLANK () )
<> BLANK (),
FORMAT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ), "Fixed" ),
BLANK ()
)
Final get :
Wish it is helpful for you!
Best Regards
Lucien
Hi @DreamToGet ,
Try to adjust your measure to below:
Output =
VAR FindOutput =
IFERROR ( SEARCH ( "Output", [Results],, 1 ), 1 )
VAR FindValue =
IFERROR ( SEARCH ( "Value", [Results], FindOutput, 1 ), 1 )
VAR FindColon =
IFERROR ( SEARCH ( ":", [Results], FindValue, 1 ), 1 )
VAR FindComma =
IFERROR ( SEARCH ( ",", [Results], FindColon, 1 ), 1 )
VAR temp =
VALUE ( FindComma - FindColon - 3 )
RETURN
IF (
IFERROR ( INT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ) ), BLANK () )
<> BLANK (),
FORMAT ( MID ( [Results], VALUE ( FindColon + 2 ), temp ), "Fixed" ),
BLANK ()
)
Final get :
Wish it is helpful for you!
Best Regards
Lucien
uh, you make it unnecessary hard on yourself. Power Query has a built-in JSON parser. Maybe you can do the transforms there?
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |