Custom Colum based on specific dates in powerquery

Question

I have a table as below, and I am trying to create Custom Colom with a following condition:

if [Date]  <  #date(2024, 7, 15) then consider all the Amount for Code (A1,A2,A3, and A4)
 if [Date] >= #date(2024, 7, 15) then consider the Amount only C1 and D1

Code    Date     Amount   Expected Amount
A1    15/06/2024    1         1
B1    15/06/2024    2         2
C1    15/06/2024    3         3
D1    15/06/2024    4         4
A1    15/07/2024    5         0
B1    15/07/2024    6         0
C1    15/07/2024    7         7
D1    15/08/2024    8         8
        

Answer 1

Your requirements don't match your expected output but this should give you an idea.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Expected", each if [Date]  <  #date(2024, 7, 15) and List.Contains({"A1", "B1", "C1", "D1"}, [Code]) then [Amount] else if [Date] >= #date(2024, 7, 15) and List.Contains({"C1", "D1"}, [Code])  then [Amount] else 0)
in
    #"Added Custom"