Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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 |
---|---|
65 | |
60 | |
47 | |
33 | |
32 |
User | Count |
---|---|
86 | |
75 | |
56 | |
50 | |
45 |