Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
viveksasi94
Helper I
Helper I

How to extract a text in a column in Direct Query mode

Hi All,

I have a column with a TEXT data type. I need to extract a string after a delimiter from this column in DIRECT QUERY mode. As FIND and SEARCH is not supported in direct query mode for calculated columns, kindly suggest a workaround for this.

 

🙂

 

 

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @viveksasi94,

 

AFAIK, most of dax functions not work on calculated column when you works on direct query mode.

In my opinion, I'd like to suggest you to use measure to instead.(notice: turn on 'allow unrestricted measures in directquery mode' option to use more dax functions in measure)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
LivioLanzo
Solution Sage
Solution Sage

Hi @viveksasi94

 

try adding this column with Power Query. Or create a view in your database with this column added

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo I'm late to the party.

Just curious, is it good idea to pre-popularize the needed calculated columns with SQL query when loading the data into Power BI? I'm still scratching my head to find the positive/negative of using SQL/M to query SQL Server databases.

@markus_zhang

 

Sometimes the calculated column can be totally reduntant (like for instance when you already have a Quantity and a Price column and you add a new one with Price * Quantity) or it can be helpful and necessary to speed up your measure (for instance a Workday indicator column in the calendar table). 

The best place to add it would be in a view within Sql server, but some people may not have the rights to create views so the second best place is within Power Query, making sure that query folding is taking place

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Got it, thanks @LivioLanzo so using view if I have the permission (I don't, alas), or Power Query.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.