Forum Discussion

Trebor84's avatar
Trebor84
Icon for Helper II rankHelper II
4 years ago

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

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 curren...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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's avatar
    Trebor84
    Icon for Helper II rankHelper II
    4 years ago

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

     

    For example FF5733 = 16734003

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      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"