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
KGrice
Solution Sage
Solution Sage

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

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

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,

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors