Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Replicate an Excel calculation in Power Query

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...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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