Forum Discussion
Powerquery add column If date in another column is more than a year ago
Hi Jo74 ,
That is something that can be done using Microsoft Power Query Formula language(informally called as M language).
Follow this documentation to learn more about M query
Power Query M formula language reference - PowerQuery M | Microsoft Learn
As mentioned by OwenAuger, the provided M query will help you in creating a new column that you want.
Please reach out to us if you have any further quries related to the provided M query or in power query in general
Dont forget to mark the helpful solution so as to help other community membersIf my response(s) assisted you in any way, don't forget to give "Kudos"
Thanks and RegardsThanks for trying this Jo.
Just to clarify, my sample M code was intended to illustrate the steps that should be added to the query after the step that produces your existing table.
The Source step in my sample should be replaced with whatever steps produced your initial table, then the Date Threshold and Added New Column steps added after this.
You could add these steps either in the Advanced Editor or by adding steps and entering code in the formula bar.
Here is a short recording of how I would apply this starting from the table you just posted, using the formula bar to enter the steps, and I have attached a PBIX with this query:
Super UserHi Jo74
I would propose something like this:
let
Source = #table(
type table [Created Date = date, Value = number],
{
{#date(2023, 12, 20), 65000},
{#date(2024, 07, 15), 10000}
}
),
DateThreshold = Date.AddYears(Date.From(DateTime.FixedLocalNow()), - 1),
#"Added New Column" = Table.AddColumn(
Source,
"New Column",
each (if [Created Date] < DateThreshold then 0.5 else 1) * [Value],
type number
)
in
#"Added New Column"Are you able to get this working in your current query?
Hi Owen
Thank you very much for taking the time to help me with this query - unfortunately not quite right, it is pulling the example figures only and not working the calculation out for each date. So a whole column of 65000 & 10000. There are over 125 rows with different dates and amounts. If the date is older than a year it should multiply the value column by 0.5 and if it is not older than a year it needs to return the value without the multiplication, see below.
Kind regards
Jo
- OwenAuger
Thanks for trying this Jo.
Just to clarify, my sample M code was intended to illustrate the steps that should be added to the query after the step that produces your existing table.
The Source step in my sample should be replaced with whatever steps produced your initial table, then the Date Threshold and Added New Column steps added after this.
You could add these steps either in the Advanced Editor or by adding steps and entering code in the formula bar.
Here is a short recording of how I would apply this starting from the table you just posted, using the formula bar to enter the steps, and I have attached a PBIX with this query:
