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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jkouloukas
Frequent Visitor

Direct Query Delimeter

After scavenging past discussions, I have yet to find a solution to an issue. Direct query is mandatory so I can't use Split Column. The DAX expressions I have found online have produced no results. I provided an attachment that shows strings separated by dashes. The dashes need to act as a delimeter that produce 4 separate columns (some values will be null). PATHITEM is not an option is this case because row-level security will be used in DirectQuery. Any help from the community would be great! Thanks guys

 

directqueryerror2 (2).JPG

 

 

1 REPLY 1
edhans
Super User
Super User

I'm really curious to see if there is a solution. Direct Query severely limits transformations so you cannot split the columns, nor use about 95% of the Text.* functions Power Query has. And you've also seen that it will limit the DAX you can use as well.

 

I think the solution will be to work with your DBA to split those columns in a SQL View that you connect to, or switch the model to Import mode. The data can definitely be split but I'm pretty sure will require SQL statements to do it. You could also write your own SQL and stick it in the Advanced Options SQL statement box, but that would be a last resort for me. A View is much preferred as you can transform after the view is brought in, just like a table, and you have no security issues in the desktop or gateway with trying to execute raw SQL code.

edhans_0-1602540424369.png

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors