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
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
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

Highlighted
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.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors