Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mimi_Ibrahim
Helper I
Helper I

Error When Using Power Query to Restrict Data

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.

1 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.

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

@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

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
Community Support
Community Support

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
Super User
Super User

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

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.