Date column all null

[Cmclaughlin2]

I am trying to load a spreadsheet into power query but the entire date column labeled "Accounting Date" is returning null. I looked through the applied changes and removed the automatic ones that would try formating it as date, and still it returned null. I even tried changing the format to text.

The dates appear this way in excel. "1/27/2024" 

Why are they all returning null? There is not a single entry that is left blank.

[Cmclaughlin2]

@v-junyant-msft @Omid_Motamedise @PhilipTreacy 

Thank you for the suggestions. While trying some of your suggestions i stumbled across some more information.

This query is automatically email out from our systems every day. The query comes as a .xls 'Microsoft Excel 97-2003 Worksheet' and when put directly into power query as is, the accounting date column shows 'null'. However when i open the Query in excel, make no changes at all but simply save it as is, i am prompted with a message i have screenshot. 

Clicking continue saves the file as is. Then loading the same file back into power query, the accounting dates appear as they should be.

You may be able to see my problem already. I do not want to have to manually save this report everyday. I just want Power BI to be able to accept it as is and automatically update my report. Do you have any suggestions how i can accomplish this? What exactly is the issue with the original format? If i can have the report sent out a different way/format, what should i tell IT i need? Or maybe, is there something i can do in power query to fix this report automatically instead of resaving it in excel?

[PhilipTreacy]

@Cmclaughlin2 

I'd suggest IT start with using a file format that isn't 20+ years old from a version of Office that is no longer supported, and hasn't been for about 10 years.

If you are loading this data from XLS files, why does the data need to be in an XLS (or XLSX) format?  I'm assuming such files are just an intermediate format before loading into PBI.  If so, can't it be exported to a CSV (or XLSX)?

Phil

[v-junyant-msft]

Hi @Cmclaughlin2 ,

Thanks for all the replies!
And @Cmclaughlin2 , Can you check if the column was already null in the Source step or did it become null in a later step?

If the Source is already null at this step, it may be a connector problem, so I recommend you to clear the cache of Power Query and try to connect again. If the subsequent steps are null, then we need to check your detailed transformation steps to find out why.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

by changing the column type, the blank cell (and if there is merge cells) will convert to null but the field cell never change to null. do you have merged cells

[PhilipTreacy]

@Cmclaughlin2 

If you are getting null then I'd say there's some filter or transformation doing it.  Can you supply the query so we can check what's happening?

Phil