Direct Query Delimeter

[jkouloukas]

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

[edhans]

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.