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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors