Can't find column on Refresh

[Anonymous]

Hello to all!

I've spent hour already looking into this and someone here can give me some light. 

I have a Power BI report that uses an Excel (used by 7 people through OneDrive Sahring) spreadsheet as a datasource. A couple of days ago the automatic refresh showed an error saying it couldn't find a specific column on the table.

The problem is: The column is there! I am looking right at it and the name is correct.

I tried everything I know but couldn't fix this. Any help is appreciated.

Best and stay safe.  

[Greg_Deckler · Accepted solution]

@Anonymous Can you post at least the start of your query using Advanced Editor? Perhaps someone inserted a column in the Excel spreadsheet and depending on how the code is written perhaps you are now not grabbing that column initially?

[Greg_Deckler · Accepted solution]

@Anonymous Can you post at least the start of your query using Advanced Editor? Perhaps someone inserted a column in the Excel spreadsheet and depending on how the code is written perhaps you are now not grabbing that column initially?

[Anonymous]

Hey there, 

I did a little digging on my own and figured what the Advanced Editor is (never used it beffore). Here are some pics of the code and a highlight of the columns presenting the error. 

One thing that calls my attention is the "#if" in the middle of the column header. I'm not familiar with the syntax of this code, so couldn't tell what this should mean. 

Best,

[Anonymous · Accepted solution]

Thanks to Greg's insight, I was able to fix this by using the Advanced Editor and deleting the "#(lf)" from the name of the column that was presenting error in all steps in which it appears. 

I was not familiar with this function nor with the M language tha PBI uses to create the tables. Good learning experience. 

Best and Stay safe everyone!

[Anonymous]

Hello Greg,

I apologize but I'm not sure I understand what you mean. I did not use any code to create the table in Power BI, I simply imported it from Excel.

Best, 

[rajendraongole1]

Hi

File -> Options and settings -> Options -> Data Load -> Clear cache

Try this approach in PBI Desktop and refreshing again.

still facing the problem just try to check the data source clear permission and reconnect the data source once more time. 

Try and let know still issue persists.

Regards,

Rajendra

[Anonymous]

Hello Rajendra, 

This didnt work, but at least it changed the error a bit. Now for one of the sheets in the excel file it says it is looking for a column "Spent Time _x000D_ (Decimal)". I have no idea where this _x000D_ came from.

Thanks