Re: Power Query If statement

mvazquez21 · ‎03-14-2021

Is there a way to simplify the following if statement? Power Query takes long to process because I have this if statement for each month. Depending on the condition, it multiplies the value by either [#"ref_HC_Data_Actuals.% of Total"], [#"ref_HC_Data_Actuals.% of Total 2"], [#"ref_HC_Data_Actuals.% of Total 3"], or [#"ref_HC_Data_Actuals.% of Total 4"].

= Table.AddColumn(#"Added FY19 Dec Final", "FY20 Jan Final", each if Text.StartsWith([Cost Center], "411") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "412") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Winston & Strawn") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Kramer Levin") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Prudential") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Charles Schwab") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Wise Consulting") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Tata Consultancy") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Microsoft") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Interstate Business Park") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 2"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Legacy Parking") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 2"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Metro Parking") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 2"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Ponte Gadea Biscayne") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 2"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"SCRS 2800 Ponce de Leon") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 2"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"600 Partners") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 3"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"FIS ") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Investran") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"DealCloud") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Sidley Austin") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"ACA Compliance") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and Text.Contains([Vendor Name],"Steven Douglas Assoc. Interim Financial") then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total 4"]

else if Text.StartsWith([Cost Center], "4") and [Account] = "62645" then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and [Account] = "62650" then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and [Account] = "62505" then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and [Account] = "62510" then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and [Account] = "62515" then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else if Text.StartsWith([Cost Center], "4") and [Account] = "62520" then [#"FY20 - Jan"] * [#"ref_HC_Data_Actuals.% of Total"]

else [#"FY20 Jan Unallocated"])

Anonymous · ‎03-14-2021

an impressive expression 😃

I don't know if my suggestion makes execution faster, but it sure is "prettier"

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pc0xDoMwDEbhq1ieWYqg7V1QhpTgP0YlsSgsPX2tpur6veFNEw8jd3zoWyuVRwaHzu3qNkcnrJDY7OYmECR6LZvttWht4f4NkESmZg3HiyOQMtnzPH7Wu1m0ZddSKUOa/l9bWTOH8AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cc = _t, vn = _t]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "func", each if Text.StartsWith([cc],"4") and List.Contains({"tizio","caio","pippo"}, [vn], (x)=>Text.Contains([vn],x)) then "it is" else "it isnt")
in
    #"Aggiunta colonna personalizzata"