Hello everybody,

Since i have a dataset with millions of rows i started using power query in excel instead of standard excel. Normally I build long formulas in excel (combinations of IF formulas, lookups and index formulas) but because of the big dataset i thought it may be a better idea to build these formulas into power querry. 

But now i have a query with multiple custom columns, that contain my formulas which takes like 30 hours to refresh. Because i am new to powerquery the formulas used in the query are build up like a normal excel formula. Which i think is one of the reasons why it takes so long to refresh the query.

Some examples of formulas used for the custom columns

=Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace([delivery zip], "-", ""), "*", ""), "**", ""), ".", "")," ", "")

=if [delivery country]= "AL" or [delivery country] = "BE" or [delivery country] = "BA" or [delivery country] = "BG" or [delivery country] = "DK" or [delivery country] = "DE" or [delivery country] = "EE" or [delivery country] = "FI" or [delivery country] = "FR" or [delivery country] = "GR" or [delivery country] = "HU" or [delivery country] = "IT" or [delivery country] = "HR" or [delivery country] = "LV" or [delivery country] = "LT" or [delivery country] = "MK" or [delivery country] = "NL" or [delivery country] = "NO" or [delivery country] = "AT" or [delivery country] = "PT" or [delivery country] = "RO" or [delivery country] = "SI" or [delivery country] = "SK" or [delivery country] = "CZ" or [delivery country] = "TR" or [delivery country] = "SE" or [delivery country] = "ES" then Text.Start([Special.char.removed delivery zip],2) else if [delivery country] = "RS" then Text.Start([Special.char.removed delivery zip],3) else if [delivery country] = "AM" then "AM" else if [delivery country] = "GE" then "GE" else if [delivery country] = "KO" then "KO" else if [delivery country] = "LU" then "LU" else if [delivery country] = "SM" then "SM" else if [delivery country] = "MD" then "MD" else "ontbreekt nog"

=if [delivery country]= "PL" then List.PositionOf(Hulpcellen_PL[zip with three digits],
Text.Start([Special.char.removed delivery zip],3)) else if [delivery country]= "CH" then List.PositionOf(Hulpcellen_CH[zip with three digits],
Text.Start([Special.char.removed delivery zip],3)) else if [delivery country]= "ME" then List.PositionOf(Hulpcellen_ME[zip with three digits],
Text.Start([Special.char.removed delivery zip],3)) else -2

=try if [loading country] = "NL" and Value.FromText([Lookup zip],"") >= 10 and Value.FromText([Lookup zip],"") <= 13 or [loading country] = "NL" and Value.FromText([Lookup zip],"") >= 20 and Value.FromText([Lookup zip],"") <= 73 then "zone 1" else if [loading country] = "NL" and Value.FromText([Lookup zip],"") >= 14 and Value.FromText([Lookup zip],"") <= 19 or Value.FromText([Lookup zip],"") >= 74 and Value.FromText([Lookup zip],"") <= 99 then "zone 2" else if [loading country] = "BE" then "zone 2" else -2 otherwise "NB"

=if [PL zip hulp] = -1 then Text.Start([Special.char.removed delivery zip],2) else if [PL zip hulp] >= 0 then Text.Start([Special.char.removed delivery zip],3) else if [delivery country]= "ES" and [Custom] = "zone 2" then [Lookup zip]&"x" else [Lookup zip]

=if Text.Start([#"PL zip."],1) = "0" or Text.Start([#"PL zip."],1) = "1" or Text.Start([#"PL zip."],1) = "2" or Text.Start([#"PL zip."],1) = "3" or Text.Start([#"PL zip."],1) = "4" or Text.Start([#"PL zip."],1) = "5" or Text.Start([#"PL zip."],1) = "6" or Text.Start([#"PL zip."],1) = "7" or Text.Start([#"PL zip."],1) = "8" or Text.Start([#"PL zip."],1) = "9" then [delivery country]&[#"PL zip."] else [#"PL zip."]

=try Boektarief_zones_Export[Zone boektarief]{List.PositionOf(Boektarief_zones_Export[Postcode], [Zip indentification])} otherwise "niet beschikbaar"

Any suggestions about how to improve the refreshing speed of this query or how to write the formulas in a more suitable way for powerquery?

Thanks in advance,

I am using windows 10 and excel 365 version 2212.