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: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

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
890 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
879 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.
Message 4 of 4
835 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
880 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
661 Views
0

Helpful resources

Announcements
April Power BI Update Carousel

Power BI Monthly Update - April 2026

Check out the April 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All