Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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?
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.
If you import the data into Power BI, the best place to split the data into multi columns is using the query editor.
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
@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
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |