How to recode date by M code when user data entry mistake ?

[Anonymous]

Hi All

My CSV file have data entry mistake , the date should 04/01/2017 but user enter wrongly .

Can some one share how to recode the date ?

My PBI file :-

https://www.dropbox.com/s/nafv47zueysvzv7/Change%202030%20to%202017%20V002.pbix?dl=0

Paul Yeo

[v-jingzhang]

Hi @Anonymous , please refer to the image below.

Best regards,

Jing

[Anonymous]

Hi Jing

That is good suggestion. in case one of the invoice sales staff enter the wrong date , and this date we have 20 invoice , how to change the date link to invoice numner ?

Paul 

[v-jingzhang]

@Anonymous With this replace values function, it will replace all the same wrong date in the selected date column with the correct one no matter what invoices they are linked to. 

Jing

[Anonymous]

Hi Sir

Assume today on 04/11/2020 , there are 100 invoice , and one of the invoice date error entry enter as 11/04/2020 , and during 11/04/2020 which is april  , also have 1-- invoice , will all also change to 04/11/2020 ?

Paul

[v-jingzhang]

@Paulyeo11

Yes, it will replace everything.

If you want to replace the specific linked to an invoice number, you can add the following M code in the Advanced Editor. Remember to change the names of the steps in the M code based on the names of the steps.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [date],each if [inv]=110102 and [date]=#date(2030,1,4) then #date(2017,1,4) else [date],Replacer.ReplaceValue,{"date"})

Best regards

Jing

[Anonymous]

Hi Jing 

this make sense

paul

[Anonymous]

@Anonymous -You can replace it with the correct date in Power Query.

[Anonymous]

Hi Sir

Can you pls share with me how to write M code , if INV = 110102 and date = 04 January 2030 , Replace by 04 January 2017 . 
Paul

[mahoneypat]

Just highlight the Date column in the query editor and click on Replace Values in the ribbon, put 1/4/2030 in first cell and 1/4/2017 in second (assuming you have no rows where 1/4/2030 would be the correct value).

Regards,

Pat