Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello eveyone,
I have a date formatting problem that masses a whole PowerBi Report. To give you a bit of context: I downnload the data from Planner using the "export plan to excel" function. Than, I come that data into another excel file that is in my sharepoint and which is the source file for my power BI report. Here the problems start:
My laptop's regional settings look like this:
1. My Excel file (stored in SharePoint) looks like this and the date was formated as "Short Date Format"
2. When I refersh my PowreBI report, It looks like this
Looks like some dates are completly messed up. Days are recognized as months and months as days, but that happens randomly. For example the first task's dates rae correct, but the next due dates are the other way around. My Regional Setting in PowerBI are set to Danish. I find it really stragne that only some cells are affected, not all of them. Any idea how to solve this?
Thank you and feel free to ask more clarification questoins if needed.
Solved! Go to Solution.
As tested, i happen to the similar problem.
To solve this problem,
in Edit queries, change the "Data type" to "text" instead of "date",
then split "start date" column by delimiter "/",
Select "start date.3", "start date.1","start date.2" in this order, then select "Tranfroms'->"Merged columns" by delimeter"/",
The same steps for [due date2] column,
finally, change the data type for these two column to "date",
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
As tested, i happen to the similar problem.
To solve this problem,
in Edit queries, change the "Data type" to "text" instead of "date",
then split "start date" column by delimiter "/",
Select "start date.3", "start date.1","start date.2" in this order, then select "Tranfroms'->"Merged columns" by delimeter"/",
The same steps for [due date2] column,
finally, change the data type for these two column to "date",
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @LostintheBIu
normally dates in Excel are stored as number and therefore the conversation should be straightforward. In your case it doesn't seem like this. Maybe it would be best that the export is done with dates formated as numbers. But if this is not possible, and in your it seems that your data is stored as text with a different format then your regional settings. In this case you can try
Date.From(_,"en-US") or a Date.FromText(_,"en-US") to transform your data. Check out this screenshot
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |