How to speed up refreshing times for my query with many custom columns

[Anonymous]

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.

[BA_Pete]

Hi @Anonymous ,

For this one:

=Text.Replace(Text.Replace(Text.Replace(Text.Replace(Tex...

...I'd need to see what the source values look like and what the expected output looked like.

For this one:

=if [delivery country]= "AL" or [delivery country] = "BE" or [delivery c...

...you can use List.Contains to tidy up a lot of this, for example:

if List.Contains( {"AL", "BE", "BA", "BG"....}, [delivery country])
then Text.Start(...etc.)

For this one:

=if [delivery country]= "PL" then List.PositionOf(Hulpcellen_PL[zip with three digits], Text.Start([Special.cha...

...again, I'd need to see the input format and understand the desired output format, but I don't think all those 'List.PositionOf' functions are hlping performance.

For this one:

=try if [loading country] = "NL" and Value.FromText([Lookup z...

...you should try and get this to evaluate without errors, rather than letting them generate then escaping them with try...otherwise.

You may also get a bit of extra performance by pre-calculating your Value.FromText values into a custom column beforehand, so you're not doing it multiple times during a calculation.

For this one:

=if [PL zip hulp] = -1 then Text.Start([Special.char.remove...

...I'm not sure there's much to be done with this. I'd probably use Text.Combine instad of the '&' concatenator, but this may just be a form improvement rather than a performance one.

For this one:

=if Text.Start([#"PL zip."],1) = "0" or Text.Start([#"PL zip."],1) ...

...again, you could tidy this up with List.Contains e.g.:

if List.Contains( {"1", "2"...}, Text.Start([#"PL zip."]))...

For this one:

=try Boektarief_zones_Export[Zone boektarief]{List.PositionOf(Boektarief_z...

...Using the List.PositionOf function within a row reference ( {...} ) modifier could be causing some performance issues. I'd need to understand source/output and what you're trying to do in between to see if this could be improved.

Pete

[Anonymous]

Hi  @BA_Pete 

First of all thanks for your answer. 

For this formula:

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

This is how the input column looks

And for the output I want to create a new column out this column where the following special characters are being removed from the string; "-", "*", "**", "".

 For this formula:

=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

I want to check if this column 

= equal to “PL” , when this is true I want to lookup the first 3 digits of the column in which the special characters have been removed from the delivery zip in the third column of another query that looks like this.

If the lookup finds the value I want to return “1” if the lookup does not find a value I want to return “-1”

For this formula:

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

I want to lookup the following column in the second column of another querry and return the value of the third column when the lookup finds a value.

I hope this gives you more insight in what I want to achieve with the formulas and how to optimize them.

I also read something about table.buffer and list.buffer to improve performance, do you know if that is something that is applicable to this case? And if it would improve the refresh time om my query?

Thanks in advance!

[BA_Pete]

Ok, makes more sense now.

I assumed the lookups were referencing previous query steps, not other queries. This will give you massive performance issues and, yes List/Table.Buffer will likely help here if used correctly, but I don't have a clear view of how everything fits together yet.

Can you provide a small sample of each of the queries that get involved please?

Maybe just 20-30 source rows of the main table with relevant columns only, and any lookup tables filtered to only include rows that I'll need to match to the main table sample, if this makes sense.

Thanks,

Pete

[Anonymous]

Hi @BA_Pete 

This is the main query, the custom columns start from the column named "Special.char.removed delivery zip" uptil the column named "Zip indetification"

main query name: Shipments_Source

The formulas in the custom columns:

Special.char.removed delivery zip =

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

Lookup 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"

PL zip hulp =

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

Custom =

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"

PL zip. =

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]

Zip indentification =

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

Boektarief zone Export =

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

lookup query name: Hulpcellen_PL

lookup query name: Hulpcellen_CH

lookup query name: Hulpcellen_ME

lookup query name: Boektarief_zones_Export

query name:

i hope this is what you are looking for. If not please let me know, and i will give some more explanation.

Thanks in advance.

[BA_Pete]

Ok. I've had a pretty good look at this and I think the key things you can quickly optimise are as follows:

Firstly, remove any special characters/numbers from your column names (I'm looking at you, [#"PL zip."]!). This prevents you from having to reference them as [#"PL zip."] (goes to just [PL zip]) which makes writing code much cleaner/easier.

[Special.char.removed delivery zip]

Text.Remove([Delivery Zip], {"-", "*", ".", ",", " "})

[Lookup zip]

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

Notice here that the escape value is null, not a text value. This allows us to explicitly set the data type of this column to number type in the next step, saving you doing Value.FromText a thousand times later.

[PL zip hulp]

Not done anything with this as I wasn't going to maually type in the supporting tables to test, but I'll reference this later when we look at buffering.

[Custom]

if [Lookup zip] = null then null
else if [Loading Country] = "NL"
    and (
        [Lookup zip] >= 10 and [Lookup zip] <= 13
        or [Lookup zip] >= 20 and [Lookup zip] <= 73
    ) then "Zone 1"
else if [Loading Country] = "NL"
    and (
        [Lookup zip] >= 14 and [Lookup zip] <= 19
        or [Lookup zip] >= 74 and [Lookup zip] <= 99
    ) then "Zone 2"
else if [Loading Country] = "BE" then "Zone 2"
else null

Notice here that I avoid error generation by evaluating for null first, and also escape to null again at the end to make subsequent steps cleaner in the same way.

[PL zip.]

As this is downstream of [PL zip hulp] I've not done anything with this for the aforementioned reason.

[Zip identification]

if List.Contains( {"0","1","2","3","4","5","6","7","8","9"}, Text.Start([PL zip], 1))
then [delivery country] & [PL zip]
else [PL zip]

[Boektarief zone Export]

As before, as this references an external table I wasn't going to manually type in, I've not done anything explicitly with this.

--Columns referencing external tables--

For all of these, I think you should buffer the relevant tables into memory before adding the column that needs them. For example, before adding [PL zip hulp], I would add three custom steps into the query:

bufHulpPL = Table.Buffer(Hulpcellen_PL),
bufHulpCH = Table.Buffer(Hulpcellen_CH),
bufHulpME = Table.Buffer(Hulpcellen_ME),

Then refer to these step names, rather than the external query names, in the new columyou add afterwards.

Similarly, before adding the [Boektarief zone Export] column, you would add the following custom step:

bufBZE = Table.Buffer(Boektarief zone Export),

Then refer to this step name instead of the external table.

Give it all a try together and let me know how you get on.

Pete

[Anonymous · Accepted solution]

Hi @BA_Pete 

Thanks a lot for your input. I get all columns working except theColumns referencing external tables.

I will try to explain what i did for this one:

bufHulpPL = Table.Buffer(Hulpcellen_PL),
bufHulpCH = Table.Buffer(Hulpcellen_CH),
bufHulpME = Table.Buffer(Hulpcellen_ME),

First i added three custom columns:

The three custom columns are named: 

"Added Custom2"

"Added Custom3"

"Added Custom4"

The code for these columns look like this:

And the tables in the columns look like this:

Next thing i did was add another custom column to lookup the value in the table.

The formula in this column looks like this:

=if [delivery country]= "PL" then List.PositionOf(#"Added Custom2"[zip with three digits],
Text.Start([Special.char.removed delivery zip],3)) else if [delivery country]= "CH" then List.PositionOf(#"Added Custom3"[zip with three digits],
Text.Start([Special.char.removed delivery zip],3)) else if [delivery country]= "ME" then List.PositionOf(#"Added Custom4"[zip with three digits],
Text.Start([Special.char.removed delivery zip],3)) else -2

This results in the following error for rows where the [delivery country]= "PL", "CH","ME":

Expression.Error: The column 'zip with three digits' of the table wasn't found.
Details:
zip with three digits

What am i doing wrong?

Thanks in advance!

[BA_Pete]

These buffered tables shouldn't be custom columns, they're custom steps.

You click this button:

Then add each step straight in the formula bar like this:

Each will create a step in your APPLIED STEPS list like 'Custom1', 'Custom2' etc. Just right-click on these names and rename to 'bufHulpPL' etc. This makes it much less confusing to reference them later.

After this, you can add your new column that references these steps, but you'll need to adjust the previous step name in your new column. For example, your query would look something like this:

let
    Source = yourSourceConnection,
    Step1 = Table.AddColumn(Source, "columName", addColumnInfo),
    bufHulpPL = Table.Buffer(Hulpcellen_PL),
    bufHulpCH = Table.Buffer(Hulpcellen_CH),
    bufHulpME = Table.Buffer(Hulpcellen_ME),
    Step2 = Table.AddColumn(Step1, "anotherColumn", columnInfoReferencingBufHulpSteps),
in
    Step2

Notice that in Step2, I've changed the first argument of Table.AddColumn to 'step1'. This ensures this new column is added to your original table rather than to a buffered one.

Pete