Replicate an Excel calculation in Power Query

[Anonymous]

Hi

I am moving an existing Excel report into Power BI and need to replicate an Excel formula in Power Query. Below screenshot shows the formla in calculated column E in Excel. 

I think it is doable by defining several steps in Power Query and adding multiple conditional columns, but I believe there should be a better way of writing a query statement that I can use, instead. Can you please help? Thank you.

[Anonymous]

Thank you heaps for your prompt answer and help. It worked properly. 🙂

Cheers

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MNE1NFTSQXBMkThGRkqxOgiFRsgKjQzgHFMgQlFoaIHdRFNdQ2Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Arrival Date" = _t, #"Start Date" = _t, #"Collection Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Arrival Date", type date}, {"Start Date", type date}, {"Collection Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DAYS", each if [Arrival Date]=null or [Start Date]=null or [Collection Date]=null then "NA" else Duration.Days([Collection Date]-List.Min({[Arrival Date],[Start Date]})))
in
    #"Added Custom"

 👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data