Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Hemant_Jaiswar
Helper I
Helper I

data load showing error

‎10-17-2024 05:02 AM

Problem with Date Parsing in Power Query

When I upload a file into Power Query, I encounter an error in the Main Sheet related to date parsing.

This issue is causing approximately 11,000 rows to be excluded or missing from the dataset because the dates in those rows are represented as --, which Power Query cannot interpret as a valid date format.

          
02-05-2024 1.33407-6971739-6777946eac79ae0-a34e-ae7b-d4e6-1969eea7734aB07WV1GTPMTV Installation Service for Mi/Redmi/Xiaomi (P)COMPLETED03-05-20248.00.00  
02-05-2024 1.18404-3495686-33283254ec79ad9-b88b-b147-bf1b-d1692b526094B0CV4KD4CGTelevision Installation Service for Hisense (P)COMPLETED03-05-20248.00.00  
02-05-2024 1.14407-8851141-337636246c79ad7-edc3-a12a-7eb4-0928778dd674B07WT3X4QDTV Installation Service (P)FULFILLMENT_HOLD03-05-20248.00.00  
02-05-2024 1.13406-2697100-16243377ec79ad7-82fb-d383-1b0f-3b19eaf48858B07WV1GTPMTV Installation Service for Mi/Redmi/Xiaomi (P)COMPLETED03-05-20248.00.00  
02-05-2024 1.01407-5841527-95883576ec79ad2-2ee1-178b-a817-b8f01ea2075cB07WV1GTPMTV Installation Service for Mi/Redmi/Xiaomi (P)COMPLETED03-05-20248.00.00  
02-05-2024 0.57404-6670138-74595197cc79ad0-0095-f2e9-89c8-75c435bb7134B07WV1GTPMTV Installation Service for Mi/Redmi/Xiaomi (P)COMPLETED03-05-20248.00.00  
02-05-2024 0.55403-0397301-000832858c79acf-1dcd-3356-e8df-07cd5de3b7e8B07WT3X4QDTV Installation Service (P)FULFILLMENT_HOLD03-05-20248.00.00  
02-05-2024 0.52404-5838739-9897123c6c79acd-b227-90e0-29a4-cb8c38bb78daB07WT3X4QDTV Installation Service (P)CANCELLED----  
02-05-2024 0.51405-8337260-784354556c79acd-54c2-23ec-d306-e4304f092a64B07WV1GTPMTV Installation Service for Mi/Redmi/Xiaomi (P)CANCELLED----  
02-05-2024 0.50402-5123177-285631786c79acc-f3af-0242-b95d-e1a18e767918B07WT3X4QDTV Installation Service (P)COMPLETED03-05-20248.00.00  
02-05-2024 0.49404-6698204-583790612c79acc-5bac-0490-dfbb-8672362c7763B0CV4KD4CGTelevision Installation Service for Hisense (P)COMPLETED03-05-20248.00.00  
02-05-2024 0.47402-9822652-954114094c79acb-61fb-3d57-9e3e-79127064974cB07WT3X4QDTV Installation Service (P)COMPLETED03-05-20248.00.00  
02-05-2024 0.45406-9610669-868354472c79aca-950a-e199-017d-b4a158fdf90cB07WT3X4QDTV Installation Service (P)COMPLETED03-05-20248.00.00  
02-05-2024 0.44408-3776720-4643508aec79aca-1449-84f9-880b-835d3cf38a57B07WV1GTPMTV Installation Service for Mi/Redmi/Xiaomi (P)CANCELLED03-05-20248.00.00  

I need to ensure that all rows are included in my analysis, even the ones with missing or invalid dates, and to fix this issue by replacing the -- values with valid dates (e.g., using previous rows' dates). How can I handle this in Power Query to resolve the error and show all the rows in the Main Sheet, including filling in the missing dates correctly?

This provides a clearer explanation of the issue while specifying the context, error details, and the desired outcome (showing all rows and replacing invalid dates).

 
 
 
 
 
 
Message 1 of 4
489 Views
0
1 ACCEPTED SOLUTION
audreygerred
Super User
Super User

‎10-17-2024 05:48 AM

Hi!

  • Go into Power Query and go to the column that has the -- instead of a date
  • Select that column
  • Go to Transform ribbon and select Replace Values
  • In the pop-up, put -- in the Value to find box and leave Replace with blank audreygerred_0-1729169060655.png
  • Click OK
  • Change the data type to Date and it should look like this at this point         

audreygerred_2-1729169164988.png

  • In the Transform ribbon, select Fill - Down audreygerred_3-1729169239798.png
  • This will fill in the nulls with whatever value was available before the blanks

audreygerred_4-1729169297399.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Message 2 of 4
478 Views
3 REPLIES 3
Omid_Motamedise
Super User
Super User

‎10-18-2024 12:30 AM

You can replaced thise -- with null, but befor that please what operation you have applied on this column which leadsd to error?

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
Message 4 of 4
434 Views
0
audreygerred
Super User
Super User

‎10-17-2024 05:48 AM

Hi!

  • Go into Power Query and go to the column that has the -- instead of a date
  • Select that column
  • Go to Transform ribbon and select Replace Values
  • In the pop-up, put -- in the Value to find box and leave Replace with blank audreygerred_0-1729169060655.png
  • Click OK
  • Change the data type to Date and it should look like this at this point         

audreygerred_2-1729169164988.png

  • In the Transform ribbon, select Fill - Down audreygerred_3-1729169239798.png
  • This will fill in the nulls with whatever value was available before the blanks

audreygerred_4-1729169297399.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Message 2 of 4
479 Views
Hemant_Jaiswar
Helper I
Helper I
In response to audreygerred

‎11-13-2024 04:58 AM

thank you for the this soluton 

Message 3 of 4
260 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All