Forum Discussion
Column Text Manipulation
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.
Community Championamitchandak 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. 😐
Super UserThank edhans for the correction.