Creating a new column based on if conditions from the combined parameters of other columns

Hi KatiS ,

Try to pivot from NC, AEGC, and FENC by TP column, then the next conditional sentence will be easier.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc/NDYAgDAXgXThzoK3VBQxxAC+EsP8a8mOLKB5oyHvhA2I0YKwJ556nywtc3STbiiPMcy95j/HPwY8DvRigOyeV1pJSp0goels0f9SiFLqSbmNVLXaPIU21kEUrp0vF6lH1gDvIAkKrdErp2236W8xVugA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project number" = _t, #"Time Period (TP)" = _t, #"Net cost (NC)" = _t, #"Actual Ext. Gross Cost (AEGC)" = _t, #"Fcst Ext Net Cost (FENC)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project number", Int64.Type}, {"Time Period (TP)", type text}, {"Net cost (NC)", Int64.Type}, {"Actual Ext. Gross Cost (AEGC)", Int64.Type}, {"Fcst Ext Net Cost (FENC)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project number", "Time Period (TP)"}, {{"Data", each _, type table [Project number=nullable number, #"Time Period (TP)"=nullable text, #"Net cost (NC)"=nullable number, #"Actual Ext. Gross Cost (AEGC)"=nullable number, #"Fcst Ext Net Cost (FENC)"=nullable number]}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Time Period (TP)"]), "Time Period (TP)", "Data"),
    #"Expanded YTD" = Table.ExpandTableColumn(#"Pivoted Column", "YTD", {"Net cost (NC)", "Actual Ext. Gross Cost (AEGC)", "Fcst Ext Net Cost (FENC)"}, {"Net cost (NC)(YTD)", "Actual Ext. Gross Cost (AEGC)(YTD)", "Fcst Ext Net Cost (FENC)(YTD)"}),
    #"Expanded HY" = Table.ExpandTableColumn(#"Expanded YTD", "HY", {"Net cost (NC)", "Actual Ext. Gross Cost (AEGC)", "Fcst Ext Net Cost (FENC)"}, {"Net cost (NC)(HY)", "Actual Ext. Gross Cost (AEGC)(HY)", "Fcst Ext Net Cost (FENC)(HY)"}),
    #"Expanded FY" = Table.ExpandTableColumn(#"Expanded HY", "FY", {"Net cost (NC)", "Actual Ext. Gross Cost (AEGC)", "Fcst Ext Net Cost (FENC)"}, {"Net cost (NC)(FY)", "Actual Ext. Gross Cost (AEGC)(FY)", "Fcst Ext Net Cost (FENC)(FY)"})
in
    #"Expanded FY"

Next, create three custom columns EXN(YTD), EXN(HY), EXN(FY). Please feel free to contact me if you need further guidance.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group