Powerquery add column If date in another column is more than a year ago

[Jo74]

Hi all

I am fairly new to Power Query and need some help please.  I have a column with a created date and if it is more than a year old the calculation is based on another column.

So If created date is older than 1 year the new column should be value column *  0.5 otherwise it should just be the value.  Is this possible?

example

Created Date	Value	New Column
20/12/2023	65000	32500
15/7/2024	10000	10000

Any suggestions greatly appreciated.

Kind regards 

Jo

[v-nmadadi-msft · Accepted solution]

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 members

If my response(s) assisted you in any way, don't forget to give "Kudos"

Thanks and Regards

[v-nmadadi-msft]

Hi @Jo74 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-nmadadi-msft]

Hi @Jo74 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Omid_Motamedise]

Hi @Jo74 , and thanks @OwenAuger .

Use this formula

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQNzTSNzIwMlbSUTIzNTAwUIrViVYyNNU3B4maAEUNDcCisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created Date" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Created Date]<Date.From(DateTime.LocalNow()-Duration.From(365)) then 0.5*[Value] else [Value])
in
    #"Added Custom"

[v-nmadadi-msft · Accepted solution]

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 members

If my response(s) assisted you in any way, don't forget to give "Kudos"

Thanks and Regards

[Jo74]

Thank you for your help.  I did not manage to get it to work and have just added a simple column after the powerquery in Excel.  Afraid this is outside my realm of understanding!

[v-nmadadi-msft]

Hi @Jo74 ,

We really appreciate your efforts and letting us know the update on the issue. If you let us know where exactly its going wrong we will look into it.

Please continue using fabric community forum for your further assistance.
Thanks and regards

[OwenAuger]

Hi @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?

[Jo74]

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 · Accepted solution]

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: