cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BenActionKPI Regular Visitor
Regular Visitor

Can't separate a field by delimiter while using direct query

Hi Everyone, 

Hoping for a solution to this one. I have a field coming from a sql server using direct query that looks like this: 001|568|222|448|78456|1145

I  need to find a way to seperate each grouping between the "|" delimiter into columns. I have already tried Pathitem, which normally would work if it wasnt direct query and i have checked the box to unrestrict dax measures while using direct query. It didnt seem to fix the fact that i still cant use that particular function. 

I have also used the "split column" button in query editor which lets me seperate the field by delimiter however i cannot save it because "it is not compatible with direct query" 

Any advice for a work around would be much appreciated! Perhaps a mlanguage script would work?? 

Cheers, 
Ben

1 ACCEPTED SOLUTION

Accepted Solutions
BenActionKPI Regular Visitor
Regular Visitor

Re: Can't separate a field by delimiter while using direct query

The sql function you refer too would create many new rows of data, I am looking to split the Path into columns. Do you have any other recommendations?

7 REPLIES 7
Phil_Seamark Super Contributor
Super Contributor

Re: Can't separate a field by delimiter while using direct query

Can you adjust the SQL you use to query the table to pivot the data into rows?  Using CROSS  APPLY or similar?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

BenActionKPI Regular Visitor
Regular Visitor

Re: Can't separate a field by delimiter while using direct query

Im not sure what you mean by this, The current SQL looks like 

"let
Source = Sql.Databases("efsdb.database.windows.net"),
efsdb = Source{[Name="efsdb"]}[Data],
dbo_WorkItem = efsdb{[Schema="dbo",Item="WorkItem"]}[Data],
#"Duplicated Column" = Table.DuplicateColumn(dbo_WorkItem, "WorkItemPath", "WorkItemPath - Copy")
in
#"Duplicated Column"

 

Ignore the duplicated column query

 

 

Capture.PNG



Community Support Team
Community Support Team

Re: Can't separate a field by delimiter while using direct query

@BenActionKPI,

 

You may specify SQL statement in the Advanced options.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql#examples

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BenActionKPI Regular Visitor
Regular Visitor

Re: Can't separate a field by delimiter while using direct query

The sql function you refer too would create many new rows of data, I am looking to split the Path into columns. Do you have any other recommendations?

BenActionKPI Regular Visitor
Regular Visitor

Re: Can't separate a field by delimiter while using direct query

The sql function you refer too would create many new rows of data, I am looking to split the Path into columns. Do you have any other recommendations?

BenActionKPI Regular Visitor
Regular Visitor

Re: Can't separate a field by delimiter while using direct query

Just so I'm clear on the direction i took. I did not find a solution to perform this task with direct query. I ended up changing connection type to Import in order to gain full dax andpowerquery functionality. 

Phil_Seamark Super Contributor
Super Contributor

Re: Can't separate a field by delimiter while using direct query

If you import the data into Power BI, the best place to split the data into multi columns is using the query editor.

 

split columns.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 417 members 4,636 guests
Please welcome our newest community members: