Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Missing dates when importing data from CSV file

Hello everyone,

I am importing environmental data from a CSV file. The column containing the Date is in text format in the CSV file.
I noticed that Power BI "confuses" month and day when formating the column as Date/Time. I have fixed that manually rearranging de date and time in a different column. 

However, that works until day 12 of the month, after that Power BI assumes I am importing a Date where the value for the Month does not exist. For example, today would be Day 11 of Month 15. As a result, Power BI is not importing these values from the Date column in the CSV file, leaving empty cells instead.

It is interesting that this does not happen with all of the CSV files from other sensors. They could have the same date type in the CSV file but Power BI would read them correctly.

Hope somebody can help me with this.

Thanks

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please have a try.

    Add a column below:

    Text.Combine({"20" & Text.End([Date], 2), Text.BetweenDelimiters([Date], "/", "/"), Text.End(Text.Start([Date], 4), 2)}, "/")

     

    Best Regards

    Community Support Team _ Polly

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please tell me if the above problem has been solved. IIf not, can you provide two screenshots about CSV date setting and Power BI Desktop date setting? If it is possible, please provide Advance Editor in Power Query.

     

    Best Regards

    Community Support Team _ Polly

     

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