Error When Using Power Query to Restrict Data

[Mimi_Ibrahim]

Hello everyone,

I have a big table which consists of more than 10mill rows of data.

In order to load the data into PBI, we use native query to restrict base on the latest 2 months, and we update the calmonth manually everytime we reach a new month. The query looks something like below:

Select * from table where calmonth in (<calmonth1>, <calmonth2>

Recently my colleague applies a power query formula (because of recurring error with native query which is annoying). The formula looks like below:

#"Filtered Rows" = Table.SelectRows(Table, each ([calmonth] = List.Max(Table[calmonth]) or [field] = List.Max(Table[calmonth])-1))
in
#"Filtered Rows"

It works fine with the whole team but me. I've got below error instead and I don't know how to fix this. Can anyone help please?

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Cloudera][ImpalaODBC] (360) Syntax error occurred during query execution: [HY000] : AnalysisException: Syntax error in line 303: where { fn convert(`calmonth... ^ Encountered: Unexpected character Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, LEFT, NOT, NULL, REPLACE, RIGHT, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error .

Thank you.

[v-janeyg-msft]

@Mimi_Ibrahim  Any updates?

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

[Mimi_Ibrahim · Accepted solution]

Hello @v-janeyg-msft,

It works now. The error refers to the calmonth field which is numeric and there is no null value for that field so it is odd when it refers to such error.

What I did was, I used August release (because latest release has a bug and I can't refresh). Then I select the field and choose 'keep errors'. It will generate a new step. 

#"Filtered Rows" = Table.SelectRows(Table,
each ([calmonth] = List.Max(Table[calmonth]) or [calmonth] = List.Max(Table[calmonth])-1)),
#"Kept Errors" = Table.SelectRowsWithErrors(#"Filtered Rows")
in
#"Kept Errors"

Then I 'apply' and it got refreshed. The first refresh was successful but the table was empty, so I tried few times until it gets populated.

After that I removed the ‘keep errors’ option and refresh again. Seems like it works fine now.

[v-janeyg-msft]

Hi, @Mimi_Ibrahim 

The code looks normal, You can try to update to the latest version.

Download Microsoft Power BI Desktop from Official Microsoft Download Center

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[amitchandak]

@Mimi_Ibrahim , Either you are getting a null as max or you are getting non-numeric value for calmonth, please check that

[Mimi_Ibrahim]

Thank you @amitchandak. I have checked that the format for the calmonth is numerical in Cloudera and it is the same in 'Transform Data'. I didn't do any change to the field type, the data just loads directly from the source table and it failed with the mentioned error.

Funny that only myself is having the error, not my other colleagues. Is there a setting that I need to change in my PBI desktop? I'm currently using August PBI release. Appreciate if you could advise, thank you.