Forum Discussion
Trebor84
Helper II
4 years agoSlow 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...
Trebor84
Helper II
4 years agoHi, yes my hex2dec gives the desired output as does your code.
For example FF5733 = 16734003
Vijay_A_Verma
Most Valuable Professional
4 years agoUse 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"