Forum Discussion

bourne2000's avatar
bourne2000
Helper V
5 years ago
Solved

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

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/1883603#M723755

 

 

Anyone can advise please?

Need Help
Show and Tell
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

2 Replies