Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 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.

 

Maz22_3-1661235664455.png

 

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

Cheers

Vijay_A_Verma
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors