Slow transformation - List.Buffer or Table.Buffer as possible solution?

[Trebor84]

Hi,

Hoping someone can help with suggestions to speed up the code below please.

This code strips out part of a number and uses a hex2decimal conversion to convert the job ref.  This is currently operating on a table with nearly 2 million rows. This part of the process is taking a long time to complete in Power Query and was wondering if there was either a List.Buffer or Table.Buffer recommendation to speed it up? Would List.Buffer allow me to buffer just the single column used for the hex2dec conversion column i.e. [Short Job Ref] ?

Also would there be a buffer solution for the table merge with my table called tblDesc, this fetches a list of lat/longs from another table that has more rows than the other table.  In total the whole process takes about 50 minutes to complete.

I have looked at the documentation for the buffer functions and it appears it helps with multiple iteration so hopefully there is a way to incorporate that into my code.

let
    Source = Folder.Files("D:\Downloads\New folder"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Order ID", type text}, {"Product Name", type text}, {"Discount", type number}, {"Sales", Int64.Type}, {"Profit", Int64.Type}, {"Quantity", Int64.Type}, {"Category", type text}, {"Sub-Category", type text}, {"Job Ref", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Short Job Ref", each Text.Start([Job Ref],6)),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "fnHex2Dec", each fnHex2Dec([Short Job Ref], null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Invoked Custom Function",{"Job Ref", "Short Job Ref"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"fnHex2Dec", "Job Ref"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Order ID"}, tblDesc, {"Order ID"}, "tblDesc", JoinKind.LeftOuter),
    #"Expanded tblDesc" = Table.ExpandTableColumn(#"Merged Queries", "tblDesc", {"lon", "lat"}, {"lon", "lat"})
in
    #"Expanded tblDesc"

Hex2dec Function also included for reference.

let
    Source = (input as text, optional base as number) as number =>
let
        values = [
                0=0,
                1=1,
                2=2,
                3=3,
                4=4,
                5=5,
                6=6,
                7=7,
                8=8,
                9=9,
                A=10,
                B=11,
                C=12,
                D=13,
                E=14,
                F=15
        ],
        digits = Text.ToList(Text.Upper(input)),
        dim = List.Count(digits)-1,
        exp = if base=null then 16 else base,
        Result = List.Sum(
                        List.Transform(
                                {0..dim}
                                , each Record.Field(values, digits{_}) * Number.Power(exp, dim - _)
                                )
                        )
in
        Result
in
    Source

Thanks

[mahoneypat]

If not already, I would first confirm that it is not the merge step slowing things down (e.g., try it without those steps). In any case, here is an example of how to incorporate List.Buffer to speed things up (more buffering may be possible inside the function too, but try this first). It uses a different function to do the conversion. You can use it or adapt your code with a similar approach. Note that the List is buffered and the function is defined within the same query. List.Buffer and Table.Buffer only help when an object is used many times within the same query.

Just create a blank query, open the Advanced Editor, and replace the code there with the below, to see how it works. I tested it on 3 million rows and it took <1 min.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnNTitWJVjIwAFNuIABmWZibOTo5uyrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hex = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hex", type text}}),
ReplaceList = List.Buffer(List.Zip({{"0".."9"}&{"A".."F"}, {0..15}})),
fxHexDec = (inputtext as text)=>
let
inputlist = List.Reverse(List.ReplaceMatchingItems(Text.ToList(inputtext), ReplaceList)),
Result =
let
b = List.Reverse(List.Positions(inputlist)),
c = List.Sum(List.Transform(b, each inputlist{_} * Number.Power(16, _)))
in
c
in
Result,
AddColumn = Table.AddColumn(#"Changed Type", "Dec", each fxHexDec([Hex]), Int64.Type)
in
AddColumn

Pat

[Trebor84]

Thanks for the suggestions, both pieces of code massivley speed up the conversions.

The merge still takes 20 minutes if anyone has a suggestion for that?

Thanks  

[Vijay_A_Verma]

Are you doing merge to perform VLOOKUP? Will you be getting a single value in every row or more than one value also?

[Trebor84]

Hi, I use this for lookup to bring back a lat and long field from my other table.

[Vijay_A_Verma]

Buffer will not be useful in your case. This code can be speeded up very much as Bottleneck is Table.AddColumn. But before that I would like to ask you a question as Table.AddColumn contains Hex2Dec.

This Hex2Dec doesn't seem to be giving right output. 

For example, for 1F, this code gives 46 as the answer whereas 31 is the right answer.

Right formula for Hex2Dec is 

List.Accumulate(Text.ToList([Data]), 0, (s,c)=> s*16 + Text.PositionOf("0123456789ABCDEF",Text.Upper(c)))

 Let me know whether your Hex2Dec is correct and that should be used.

[Trebor84]

Hi, yes my hex2dec gives the desired output as does your code.

For example FF5733 = 16734003

[Vijay_A_Verma]

Use the below code to overcome performance bottleneck. (For improving performance further, you can use my formula for conversion. It will avoid overhead of calling a function and is also shorter. In below code, I have used your function)

let
    Source = Folder.Files("D:\Downloads\New folder"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Order ID", type text}, {"Product Name", type text}, {"Discount", type number}, {"Sales", Int64.Type}, {"Profit", Int64.Type}, {"Quantity", Int64.Type}, {"Category", type text}, {"Sub-Category", type text}, {"Job Ref", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Short Job Ref", each Text.Start([Job Ref],6)),
	BuffList = List.Buffer(#"Added Custom"[Short Job Ref]),
   ListCount = List.Count(BuffList),
    GenHex2Dec = List.Generate(()=>[x=fnHex2Dec(BuffList{0}),i=0], each [i]<ListCount, each [i=[i]+1, x=fnHex2Dec(BuffList{i})], each [x]),
    Result = Table.FromColumns(Table.ToColumns(#"Added Custom")&{GenHex2Dec},Table.ColumnNames(#"Added Custom")&{"fnHex2Dec"}),
    #"Removed Columns1" = Table.RemoveColumns(Result,{"Job Ref", "Short Job Ref"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"fnHex2Dec", "Job Ref"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Order ID"}, tblDesc, {"Order ID"}, "tblDesc", JoinKind.LeftOuter),
    #"Expanded tblDesc" = Table.ExpandTableColumn(#"Merged Queries", "tblDesc", {"lon", "lat"}, {"lon", "lat"})
in
    #"Expanded tblDesc"