Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mahenkj2
Impactful Individual
Impactful Individual

how to format dates of different excel files in power query

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.

1 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

PijushRoy_0-1707829017828.png

 

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

View solution in original post

8 REPLIES 8
mahenkj2
Impactful Individual
Impactful Individual

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!

@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
Community Champion
Community Champion

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
Impactful Individual
Impactful Individual

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.

 

 

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
Impactful Individual
Impactful Individual

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:

mahenkj2_0-1707786255985.png

 

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

 

mahenkj2_1-1707786377186.png

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
Impactful Individual
Impactful Individual

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.

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

PijushRoy_0-1707829017828.png

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors