Trim Function in Query Editor When Using Power BI Direct Query [Impala]

[funnDewer]

When using Power BI direct query mode, I am unable to perform the trim string transformation function found in the query editor.
I am using the Impala (beta) driver.

To reproduce:
Connect to server using Impala driver in direct query mode
Create a live connection to a string table.
Open up query editor and right click on the string column
Look under "Transform" and click on "Trim"
-> Power BI would show a warning "This step results in a query that is not supported in DirectQuery mode"

Any help would be appreciated!

Best,

FD

[funnDewer]

Help anyone?

[v-yuezhe-msft]

Hi @funnDewer,

The Impala connector is still in Preview,  there are a few limits. I suspect that Trim feature is one of the limits.

Thanks,
Lydia Zhang

[funnDewer]

I manaully ran a query to the impala server UI and it can trim properly.

RTRIM(LTRIM(my column)) 

FD

[v-yuezhe-msft]

Hi @funnDewer,

In Direct Query mode, Power BI Desktop has restricted features.

In your scenario, you can right click you table in Report view, choose “New Column” , then enter the following formula(replace column name with your own). This way, you will trim space for your original column.

TrimmedColumn = TRIM(test[VENDOR])

Thanks,
Lydia Zhang

[funnDewer]

Hi Lydia,

That didn't work 😞

I got the column with trim function created but when I create a Table visualization (like the one you have), it just shows the following error: 

"Can't dispay the visual. See details"

and when I click on the "See details", I see "...We couldn't fold the expression to the data source. Please try a simpler expression..".

Any idea why this is happening? 

Best,

FD

[funnDewer]

Thanks, I'll try it out.

I am curious why the "SQL Server Database" connector in direct query mode the trim function works with the steps in my post?

Is it because they have special settings or connector with "beta" labelled are restricted?

Thanks again,

FD