Forum Discussion
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
7 Replies
- Phil_Seamark
Microsoft Employee
Can you adjust the SQL you use to query the table to pivot the data into rows? Using CROSS APPLY or similar?
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-msftCommunity Support
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
- 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.
