how to format dates of different excel files in power query

[mahenkj2]

Hi,

I am merging multiple excel sheets from a folder, some have date format as 'Date' in the sheet and some are in 'General' format. I am merging all of them in one query, but with error in date column. General format are changed to date, but then 'Date' formatted values converetd to date with error.

I need to automate in a way that any of these format can successfully be converted to date in same column without chaging the source files.

Both format works well when imported individually, reason is general format automatically converted to date, and 'Date' format first converted to number and then in date without error.

The setting to locale does not work becuase there are multiple format in same power query column.

Thanks for support.

[mahenkj2]

Hi @PijushRoy 

Though I accepted your suggestion as solutuin and infact it worked the other day, but I see the error again since today.

I am fethcing these excel files from a folder, and as you know there is common transformation in one of the file first in power query (helper queries) and then main query is used. I am not sure how the one of sheet is being used in sample file transformation (which file), but my error seems to be dependent on that.

As I said, one file have dates in Proper date format, which is changeable to whole number whereas in other file it has General format. Whole number can directly transformed to dates, and general format can be first transformed to text and then dates.

Currently, my sample file trnasformation query have date in Date/whole number format and if I apply your method to first tx in text and then in Dates with locale, it gives the error (DataFormat.Error: We couldn't parse the input provided as a Date value.)

I am still not finding how to tackle this!

[PijushRoy]

@mahenkj2 

What is the source of the excel file means
Excel file created by user manually or
It is generated from any software or erp system

Please find the new file link 
Use Excel.Workbook function https://www.youtube.com/watch?v=l_CgIOwSjmU

If your requirement is solved, please mark THIS ANSWER also as SOLUTION

[PijushRoy]

Hi @mahenkj2 

What is the different format of the Date in all Excel file? i.e. DD/MM/YYYY or MM/DD/YYYY or anything else?
Please share details
Once you merge all excel files, Power BI automatically creates Change Type steps. So recommendation is, please delete the automatic Change Type step. Use the Power BI locale (https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query ) option as per your date format for Excel to overcome any error in the Date column.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[mahenkj2]

1. One Excel file have date in General format, and dates appear as dd-mm-yyyy.

2. Another have date in date format and dates appear as dd-mm-yyyy.

Individually they work fine. I convert first one to text and then in date in power query, no error. For second one, I convert to number and then in date, it works fine.

When both are merged from folder, any of the way as above does not work for one of the type. So one file always give error.

I will provide sample data in sometime if my above explanation is not enough.

[PijushRoy]

Hi @mahenkj2 

I understand your concern.
If you shared both files with some sample data, it would be helpful to reproduce the issue

Thanks

[mahenkj2]

Hi @PijushRoy 

I submit below screenshot of 2 excel files, dates are in different formats in both:

1. here u see dates in Date format, u can see count/sum etc for this field below highlighted, so these are like numbers:

2. In second screen shot below, u see dates in General format and can not see smummary statistics other than counts:

Appearance wise, bot files have dates in dd-mm-yyyy format. Can u suggest how to tackle this situation. 

I am not able to replicate the problem becuase of some other transformation before merging. So if u can suggest at least on source side how to ensure same datatype.

[mahenkj2]

Hi @PijushRoy ,

Please find the link:

https://drive.google.com/drive/folders/1BST-1PNJJpq3Cm91yqTkhN2qcdND51mw?usp=sharing

Excel formats are same as I am using the with this sample data, I dont see the error in Power BI file, so I could not replicate the problem. But you may have a look at excel file formats and suggest how to care for such difference in date columns.

[PijushRoy · Accepted solution]

Hi @mahenkj2 
Make the column as Text and use the locale function which fits for your excel Date format.
If one file is Date format and another General format, there is no problem.
Only need the same order MM-DD-YYYY or DD-MM-YYYY
also when using the LOCALE function, check which country format fits with the data format

If your requirement is solved, please mark THIS ANSWER as SOLUTION