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

[KatiS]

Hi everyone,

I have a bit of a complicated situation where I would like to create a conditional column called Ext Net Cost (EXN) - the last column below, based on the following sample data table and if statements below the table. 

The Sample data is as follows:

Project number	Time Period (TP)	Net cost (NC)	Actual Ext. Gross Cost (AEGC)	Fcst Ext Net Cost (FENC)	Ext Net Cost (EXN)
1	YTD	0	10	0	0
1	HY	0	10	0	0
1	FY	0	0	0	0
2	YTD	0	10	0	0
2	HY	0	10	10	0
2	FY	0	0	10	0
3	YTD	60	30	0	30
3	HY	30	10	10	10
3	FY	0	0	0	0
4	YTD	200	70	0	70
4	HY	50	50	50	50
4	FY	250	0	200	200
5	YTD	300	150	0	150
5	HY	100	100	100	100
5	FY	500	0	120	150
…

The if statements are:

if NC HY = 0 and NC FY =0 then EXN FY/HY/YTD = 0 (read as if value of NC for HY period is zero and value of NC for for FY period is zero then return 0 for all periods in the EXN column)

else

if NC HY > 0 and NC FY =0 then EXN FY = 0 and EXN HY = AEGC HY (read as if value of NC for HY period is > zero and value of NC for for FY period is zero then return 0 in the FY row of the EXN column and return the value from the AEGC column for HY period in the HY row if the EXN column etc.)

else

if NC HY > 0 and NC FY >0 and AEGC YTD <= FENC FY then EXN FY = FENC FY, EXN HY = AEGC HY

else

if NC HY > 0 and NC FY >0 and AEGC YTD > FENC FY then EXN FY = AEGC YTD, EXN HY = AEGC HY

else

EXN YTD = AEGC YTD

Please note this conditional evaluation should be done for each project in the list and the number of projects and project numbers are variables that can change so specific project numbers should not be included in the code. 

I have been struggling with this so your help will be greatly appreciated!

Thank you

[Anonymous · Accepted solution]

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

[KatiS]

Hi Gao,

This way of looking at it simplifies it significantly and it is easy to apply the if conditions and can unpivot after. It worked. Thank you!  🙂 

Kat