Expression not recognized when adding a custom column in Query Editor

[stats111]

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,

[KGrice · Accepted solution]

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.

[stats111]

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]

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.