Forum Discussion
Column Text Manipulation
Hi All,
I have 2 Tables that I am trying to join from SQL, however the Data is very poorly designed ( I have no way to amend as is part of 3rd Party software). The join is part text from one column is Substring(columnname, 8,3) say to the complete field of the second table.
I am trying to perform some field level text manipulaiton within Power Bi to break out the text into a seperate column and then would like to build the relationship from this column to the other table. I have tried using the Extract option in the power query editor as well as trying to use Text.Middle function however both give me a warning "This step results in a query that is not supported in Direct Query Mode".
Does anyone have any idea how I can acheive this in Power Bi ?
Many thanks
Tristan
Unfortunately most text manipulation steps in Power Query do not work with Direct Query. Your only two alternatives are to fix the data a the source (which you said wasn't possible), or switch to Import Mode, which will let you do just about anything you want.
Even in DAX there are features that become limited with Direct Query. It is mean for very specific purposes. Generally Import is the way to write most reports.
5 Replies
Unfortunately most text manipulation steps in Power Query do not work with Direct Query. Your only two alternatives are to fix the data a the source (which you said wasn't possible), or switch to Import Mode, which will let you do just about anything you want.
Even in DAX there are features that become limited with Direct Query. It is mean for very specific purposes. Generally Import is the way to write most reports.
Thank you edhans for the prompt response 👍
In Direct query mode, you can not create custom columns.
refer: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery
amitchandak you can create custom columns in Direct Query. It just has to correspond to what is allowed. I do it all of the time. Even fairly complex if/then/else constructs can be folded back to the server.
Unfortunately though most Text.* functions do not work. Text.Trim() does, but very few others do, including Text.Start, Text.Middle, and Text.End. 😐
Thank edhans for the correction.
