Direct Query and Custom Function

[iqb]

I have one query like,

let
SomeText = " AND ( " &
(
if SomeParameter= "All" then " 1=1 "
else if SomeParameter= "1" then " T.Column BETWEEN 1 AND 10"
) & ") "
in
AgeText

Then I right click and Create function. 

Now I have another direct query when I call this `FunctionI(SomeParameter)`, I get `This query contains transformations that can't be used for DirectQuery.`

[v-yalanwu-msft · Accepted solution]

Hi, @iqb ;

This is a limitation of DirectQuery mode, I am afraid that you need to switch to Import mode to make the function work.

Also, you can check if this funtion needs to be modified to the following:

let
SomeText = " AND ( " &
(
if SomeParameter= "All" then " 1=1 "
else if SomeParameter= "1" then " T.Column BETWEEN 1 AND 10"
else null
) 
& ") "
in
SomeText

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[iqb]

Even Table.FromList is failing in Direct Query