power query editorexcel

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Power Query Editor-Excel

Power Query Editor-Excel

resolvedResolved · Low Priority · Version 365

Paul has attended:
Excel Advanced - Formulas & Functions 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

Edited on Fri 3 Dec 2021, 15:34

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

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Create Charts with One keystroke

Create a graph with one click

1. Select your data.
2. Press F11.
3. You have a graph.

View all Excel hints and tips


Server loaded in 0.12 secs.