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

[Anonymous]

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

[Anonymous]

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]

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

[Phil_Seamark]

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

[Anonymous]

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

[v-chuncz-msft]

@Anonymous,

You may specify SQL statement in the Advanced options.

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

[Anonymous · Accepted solution]

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?

[Anonymous]

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?