data load showing error

[Hemant_Jaiswar]

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.33	407-6971739-6777946	eac79ae0-a34e-ae7b-d4e6-1969eea7734a	B07WV1GTPM	TV Installation Service for Mi/Redmi/Xiaomi (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 1.18	404-3495686-3328325	4ec79ad9-b88b-b147-bf1b-d1692b526094	B0CV4KD4CG	Television Installation Service for Hisense (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 1.14	407-8851141-3376362	46c79ad7-edc3-a12a-7eb4-0928778dd674	B07WT3X4QD	TV Installation Service (P)	FULFILLMENT_HOLD	03-05-2024	8.00.00
02-05-2024 1.13	406-2697100-1624337	7ec79ad7-82fb-d383-1b0f-3b19eaf48858	B07WV1GTPM	TV Installation Service for Mi/Redmi/Xiaomi (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 1.01	407-5841527-9588357	6ec79ad2-2ee1-178b-a817-b8f01ea2075c	B07WV1GTPM	TV Installation Service for Mi/Redmi/Xiaomi (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 0.57	404-6670138-7459519	7cc79ad0-0095-f2e9-89c8-75c435bb7134	B07WV1GTPM	TV Installation Service for Mi/Redmi/Xiaomi (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 0.55	403-0397301-0008328	58c79acf-1dcd-3356-e8df-07cd5de3b7e8	B07WT3X4QD	TV Installation Service (P)	FULFILLMENT_HOLD	03-05-2024	8.00.00
02-05-2024 0.52	404-5838739-9897123	c6c79acd-b227-90e0-29a4-cb8c38bb78da	B07WT3X4QD	TV Installation Service (P)	CANCELLED	--	--
02-05-2024 0.51	405-8337260-7843545	56c79acd-54c2-23ec-d306-e4304f092a64	B07WV1GTPM	TV Installation Service for Mi/Redmi/Xiaomi (P)	CANCELLED	--	--
02-05-2024 0.50	402-5123177-2856317	86c79acc-f3af-0242-b95d-e1a18e767918	B07WT3X4QD	TV Installation Service (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 0.49	404-6698204-5837906	12c79acc-5bac-0490-dfbb-8672362c7763	B0CV4KD4CG	Television Installation Service for Hisense (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 0.47	402-9822652-9541140	94c79acb-61fb-3d57-9e3e-79127064974c	B07WT3X4QD	TV Installation Service (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 0.45	406-9610669-8683544	72c79aca-950a-e199-017d-b4a158fdf90c	B07WT3X4QD	TV Installation Service (P)	COMPLETED	03-05-2024	8.00.00
02-05-2024 0.44	408-3776720-4643508	aec79aca-1449-84f9-880b-835d3cf38a57	B07WV1GTPM	TV Installation Service for Mi/Redmi/Xiaomi (P)	CANCELLED	03-05-2024	8.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).

[Omid_Motamedise]

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

[audreygerred · Accepted solution]

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 
• Click OK
• Change the data type to Date and it should look like this at this point         

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

[Hemant_Jaiswar]

thank you for the this soluton