Paul has attended:
Excel Advanced - Formulas & Functions course
Power BI Reporting course
Customer Service Excellence course
Power Query Editor-Excel
Trying to add a conditional column in power query, driven from a date column in relation to todays date (todays date dynamic when report is run)
Something like this (todays date = 1-12-2021)
Column Date resulting conditional column
01/12/2021 0-6 days
03/12/2021 0-6 days
07/12/2021 0-6 days
12/12/2021 7-13 days
15/12/2021 14-20 days
18/12/2021 14-20 days
22/12/2021 21-27 days
26/12/2021 21-27 days
30/12/2021 28+ days
05/01/2022 28+ days
Categories (look up) is this..
0-6 days
7-13 days
14-20 days
21-27 days
28+ days
Any suggestions, Thanks
RE: Power Query Editor-Excel
Thank you for the forum question.
Interesting challenge.
Once you have the date column in Power Query you first need to add a custom column of =DateTime.LocalNow () which will give you today's date. Next add custom column of =[Custom] - [Dates] and then Change type of this column to Whole number. This column will give you the date differences in days.
Next add a further custom column containing
= Table.AddColumn(#"Changed Type2", "Custom.2", each if [Custom.1] <= 6 then "0-6 Days" else if [Custom.1] <= 13 then "7-13 Days" else if [Custom.1] <= 20 then "14-20 Days" else if [Custom.1] <= 27 then "21-27 Days" else if [Custom.1] >= 28 then "28+ Days" else null)
This final column should give you what you are looking for. Then rename columns / delete columns as required to tidy up afterwards.
Please see attach file as example.
Mike Hodkinson
MS Office Consultant Trainer
