cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stats111
Regular Visitor

Expression not recognized when adding a custom column in Query Editor

I'm trying to add a column with the following formula:

 

 

SEARCH (" ", [Description], 1, BLANK ())

 

 

 However I get the error message:

 

Expression error: The name 'SEARCH' wasn't recognized.  Make sure it's spelled correctly.

 

The same query works when using the data view. I thought queries in both areas are interchangeable?

 

Thanks,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
KGrice Solution Sage
Solution Sage

Re: Expression not recognized when adding a custom column in Query Editor

Editing in the Query Editor relies on the M language, while doing so in the Data View relies on DAX. They're not interchangeable.

 

The M equivalent is Text.PositionOf. For comparison, here is the SEARCH function for DAX.

View solution in original post

3 REPLIES 3
Highlighted
KGrice Solution Sage
Solution Sage

Re: Expression not recognized when adding a custom column in Query Editor

Editing in the Query Editor relies on the M language, while doing so in the Data View relies on DAX. They're not interchangeable.

 

The M equivalent is Text.PositionOf. For comparison, here is the SEARCH function for DAX.

View solution in original post

stats111
Regular Visitor

Re: Expression not recognized when adding a custom column in Query Editor

Hi KGrice,

 

Thank you for the response. Can you tell me what are the relative benefits of transforming data at either points i.e. at load time, vs after data load? If I re-load the data, would I lose any transformation made in the latter?

 

Thanks,

KGrice Solution Sage
Solution Sage

Re: Expression not recognized when adding a custom column in Query Editor

Hi @stats111. I tried to find some general guidelines on this, but I can only find recommendations, not anything specifically tied to performance. As a general rule, MS's documentation on Power Query vs Power Pivot shows using Power Query (Get Data in Power BI, or the Query Editor) for getting/shaping your data, and Power Pivot (Data View in Power BI, writing measures and adding calculated columns) for extending your analysis capabilities.

 

With that in mind, there are some times when you will definitely prefer one over the other, depending on what you want to do. Avi Singh outlines some best practices on PowerPivotPro. For something that will just be a static column, I think you're fine either way. You also won't lose any transformations/additions you make in the data view after you reload your data; anything there will update based on the new dataset.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors