Getting DataFormat.Error: We couldn't convert to Number.

[bourne2000]

Hi

I am having two dates such as hire date and terminated date. I am trying to merge these two dates into my calendar table for my analysis. I am using below M Query to create an additional column with one date per cell between the Hire date and Terminated date. Then I am planning to connect this date column into my calendar table.

{Number.From([Hire Date])..Number.From([Terminated date])}

However, I am getting below error when I used the above formula

Both my Hire date and Terminated date is date type.

Please note, yesterday I posted this in the community and I got the reply. When I tried with my data, it's not working. Link to the yesterday's query: https://community.powerbi.com/t5/Desktop/How-to-create-a-calendar-table-for-two-date-columns/m-p/188...

Anyone can advise please?

[v-deddai1-msft · Accepted solution]

Hi @bourne2000 ,

What's the regional settings in your pbix file?

Or try to convert it to "en-US" format:

= Table.TransformColumnTypes(your last step, {{"datecolumnname", type date}}, "es-US")

Then use the 

{Number.From([Hire Date])..Number.From([Terminated date])}

It will works as your expection.

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

Best Regards,

Dedmon Dai

[amitchandak]

@bourne2000 , I am not sure why you need that if your dates are of date data type.

Refer to my blog on the same topic, if that can help 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...