Power Query 中"Count Ifs"的加载时间

Question

我正在优化一个excel报告，我有三个不同来源的数据(其中两个很大)。我正在使用power query提前完成所有的sumifs和计数，以尽量减少excel中的计算。

问题是，它似乎运行更慢。以下是我做的计数:

自定义列:

List.Count(
Table.SelectRows(
damages,
(InnerTable) => InnerTable[locator_id] = [Trainee]
and 
InnerTable[defect date] <= [Date of the Training]
)[workday_id]
)

这个列被添加到一个有1200行的表中。我从(损害)中计算的表是两个附加的表，总计80,000行。运行这个查询需要花费超过10分钟的时间(仅使用这个计算，还没有添加其他计算)。在底部有一个计数器，总计加载的大小，它在加载之前超过8GB(尽管我所有的csv总共是100 mb)。

我注意到一件事，在构建查询时，它非常快，直到我添加List.Count。看来查询的最后一个方面明显地减慢了速度。

我试过玩周围做一个表。而不是将其转换为列表并进行计数。我已经尝试了各种设置的损害表，既加载到数据模型，而不是。快速加载似乎也没有什么不同。

我只是用快速加载计时。加载前25行花了32秒，这还只是一次计算。

编辑:我也拿出了损害表的附录。性能没有变化。

编辑#2:根据下面的请求，附上示例数据和查询。
它经过了简化和净化，但是它有同样的性能问题。预览和加载都非常慢。

我不知道如何附加文件，但需要的是在dropbox的链接。https://www.dropbox.com/sh/xa9rja1soj2aysi/AADnw_yC7huumERlr9VQpp2ma?dl=0

查询# 1:

let
Source = Csv.Document(File.Contents("C:UsersTristenHannahDropbox (USICLLC)Trainingquality firsttraining reportingslow querydamagetable1.csv"),[Delimiter=",", Columns=52, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"numcol1", Int64.Type}, {"textcol1", type text}, {"numcol2", Int64.Type}, {"textcol2", type text}, {"textcol3", type text}, {"dateforcalc1", type date}, {"textcol4", type text}, {"datecol1", type datetime}, {"textcol5", type text}, {"textcol6", type text}, {"textcol7", type text}, {"textcol8", type text}, {"textcol9", type text}, {"textcol10", type text}, {"textcol11", type text}, {"textcol12", type text}, {"textcol13", type text}, {"datecol2", type datetime}, {"datecol3", type datetime}, {"numcol3", Int64.Type}, {"numcol4", Int64.Type}, {"textcol14", type text}, {"textcol15", type text}, {"textcol16", type text}, {"textcol17", type text}, {"textcol18", type text}, {"textcol19", type text}, {"textcol20", type text}, {"textcol21", type text}, {"numcol5", Int64.Type}, {"numcol6", Int64.Type}, {"numcol7", Int64.Type}, {"numcol8", Int64.Type}, {"datecol4", type date}, {"textcol22", type text}, {"textcol23", type text}, {"textcol24", type text}, {"textcol25", type text}, {"textcol26", type text}, {"datecol5", type datetime}, {"dateforcalc2", type datetime}, {"textcol27", type text}, {"textcol28", type text}, {"textcol29", type text}, {"textcol30", type text}, {"employeeid", Int64.Type}, {"textcol31", type text}, {"textcol32", type text}, {"numcol9", Int64.Type}, {"numcol10", Int64.Type}, {"textcol33", type text}, {"textcol34", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"numcol1", "textcol2", "textcol3", "textcol4", "datecol1", "textcol5", "textcol6", "textcol7", "textcol8", "textcol9", "textcol10", "textcol11", "textcol12", "textcol13", "datecol2", "datecol3", "numcol3", "numcol4", "textcol14", "textcol15", "textcol16", "textcol17", "textcol18", "textcol19", "textcol20", "textcol21", "numcol5", "numcol6", "numcol7", "numcol8", "datecol4", "textcol22", "textcol23", "textcol24", "textcol25", "textcol26", "datecol5", "textcol27", "textcol28", "textcol29", "textcol30", "textcol31", "textcol32", "numcol9", "numcol10", "textcol33", "textcol34"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "dateforcountifs", each if [dateforcalc2] = null then [dateforcalc1] else [dateforcalc2]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"dateforcountifs", type date}})
in
#"Changed Type1"

查询# 2

let
Source = Csv.Document(File.Contents("C:UsersTristenHannahDropbox (USICLLC)Trainingquality firsttraining reportingslow querymaintable.csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Start time", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type text}, {"EmployeeID", Int64.Type}, {"Number", Int64.Type}, {"JoinDate", type date}, {"Number2", type number}, {"Number3", Int64.Type}, {"Text1", type text}, {"Number4", Int64.Type}, {"Number5", Int64.Type}, {"Number6", Int64.Type}, {"Number7", Int64.Type}, {"Text2", type text}, {"Text3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count(
Table.SelectRows(
damagetable1,
(InnerTable) => InnerTable[employeeid] = [EmployeeID] and InnerTable[dateforcountifs] <= [JoinDate]
)[employeeid]
))
in
#"Added Custom"

Answer 1

基于您提供的文件。

在Excel 365 64位win10上花了大约20秒

M代码:

let
Source = Csv.Document(File.Contents("C:UsersRicardoDiazDescargasslow querymaintable.csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Start time", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type text}, {"EmployeeID", Int64.Type}, {"Number", Int64.Type}, {"JoinDate", type date}, {"Number2", type number}, {"Number3", Int64.Type}, {"Text1", type text}, {"Number4", Int64.Type}, {"Number5", Int64.Type}, {"Number6", Int64.Type}, {"Number7", Int64.Type}, {"Text2", type text}, {"Text3", type text}}),
Merge = Table.NestedJoin(#"Changed Type", {"EmployeeID"}, damagetable1, {"employeeid"}, "damagetable1", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "damagetable1", {"dateforcountifs"}, {"dateforcountifs"}),
RemoveOther = Table.SelectColumns(Expand,{"EmployeeID", "JoinDate", "dateforcountifs"}),
Order = Table.Sort(RemoveOther,{{"EmployeeID", Order.Ascending}, {"JoinDate", Order.Ascending}, {"dateforcountifs", Order.Ascending}}),
AddConditions = Table.AddColumn(Order, "less than", each if [dateforcountifs] <= [JoinDate] then true else null),
Group = Table.Group(AddConditions, {"EmployeeID", "JoinDate", "less than"}, {{"Recuento", each Table.RowCount(_), Int64.Type}}),
Filter = Table.SelectRows(Group, each ([less than] = true)),
RemoveOther2 = Table.SelectColumns(Filter,{"EmployeeID", "JoinDate", "Recuento"})
in
RemoveOther2