Speeding Up Custom Query - Unique Prefixer

Luarana
Posts: 2
Joined: Wed Aug 09, 2017 10:57 am

Speeding Up Custom Query - Unique Prefixer

Postby Luarana » Wed Aug 09, 2017 11:39 am

Hi all,

New to the forums. I've got the below Custom Query and it's taking too long to run. 30 mins + in real work cases.

Basically users submit codes but sometimes they aren't unique and they need to be.

My problem is... it's super fast with low numbers. But exponentially slower above a few hundred lines of data.

I was wondering if anyone knew of a different way to achieve the same outcome or a way to see why my code is so slow?

I also have a side issue. If for eg. ABC/1 already exists and there are 2x ABC. And i change one to ABC/1 is there a way for me to write code to check that in the same query? Currently i'm just doing 3 passes of the same query. Which i just run for 1 - 2 hours in the background against 5k + lines of data.

Sample Table:

Identifying Code Index Unique Code
ABC 0 ABC
ABC 1 ABC/1


Custom Query:

let
Value = [#"Identifying Code"],
IndexValue = [Index],

Table = Table.SelectRows(Table.SelectColumns( #"Added Index", {"Identifying Code" , "Index"} ), each _ [#"Identifying Code"] = Value),

CountTable = Table.RowCount(Table.SelectRows(Table, each _ [Index] < IndexValue )),

UniqueVal = if CountTable = 0 then Value else Value &"/"& Text.From(CountTable)

in
UniqueVal
Attachments
Prefix Alt Codes.xlsx
(16.93 KiB) Downloaded 35 times

User avatar
AndVGri
Posts: 31
Joined: Thu Jun 02, 2016 2:41 am
Location: Sankt-Peterburg

Re: Speeding Up Custom Query - Unique Prefixer

Postby AndVGri » Wed Aug 09, 2017 4:20 pm

Hi
Try do that with group algorithm.
Regards,
Attachments
Prefix Alt Codes.xlsx
(21.57 KiB) Downloaded 39 times

Luarana
Posts: 2
Joined: Wed Aug 09, 2017 10:57 am

Re: Speeding Up Custom Query - Unique Prefixer

Postby Luarana » Wed Aug 09, 2017 5:08 pm

Sweet.

Thanks for that AndVGri.

Just for my own education and others. I believe my original code which i've forgotten exactly worked by Indexing every item. And then checked for Codes that are the same but with a lower index. I.e. ABC Index 0 no matches but ABC Index 1 will match 0 and 1. It would then Prefix when it matched more than 1. As you moved up the indexes the more matches the higher the prefix.

The new code suggested by AndVGri is really good as it eliminates the row.count component which i believe is slowing my query down.

All it does is group by the codes. and then indexes the group. If Index is higher than 0 within the group it needs a prefix. This should still allow what i believe to be query folding or something and not slow down the query.

I'll field test and post results.


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests

cron