PowerQuery solving date value to retain

hannouech

New Member
Joined
Jan 7, 2019
Messages
4
Hello,

I have a table tracking the invoices from suppliers of the month of feburary, but there are some invoices of month of january received in februaray, I have to replace them by 01/02/2018.

I have to solve this by powerquery, so when I import the transactions of march, the invoices with dates before 01/03/2018 have to be replaced by 01/03/2018 and so on for the following month.

Thanks a lot

This a link to the file

https://www.cjoint.com/c/IAhnPFMEw8A
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
My employer blocks access to the link you posted.
I created a table of dates in a column labeled "MyDates" and included this M-code in my query:
Code:
= Table.TransformColumns(#"Changed Type",{{"MyDates", each if _ < #date(2019,2,1) then #date(2019,2,1) else _, type date}})

Is that something you can work with?
 
Last edited:

hannouech

New Member
Joined
Jan 7, 2019
Messages
4
My employer blocks access to the link you posted.
I created a table of dates in a column labeled "MyDates" and included this M-code in my query:
Code:
= Table.TransformColumns(#"Changed Type",{{"MyDates", each if _ < #date(2019,2,1) then #date(2019,2,1) else _, type date}})

Is that something you can work with?

It dosent work :(

Would you please make it on the file and send it to me???
 

Forum statistics

Threads
1,148,108
Messages
5,744,874
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top