locked
Is there any possible that I can realize the functionality of "SUMIF" in the Power Query(Query Editor)? RRS feed

  • Question

  • I got "Date" and "Time" column from database and I've already load the data to the Power Query(Query Editor). I want to add a computed column that can get the same result that shows in the following "Total" column right in the Query Editor, instead of loading the data to the worksheet and using the SUMIF like what I did in the figure.  

    Is there any possible that I realize this?

    BTW, the reason I want to do this is because I'm dealing with more than 10 thousands lines' record. If I use the SUMIF and do it in the worksheet, it will take too long time to process.

    Thursday, December 25, 2014 5:55 AM

Answers

  • hi,

    I tested this code with 10 thousands lines. The performance of this code is very bad. Actually, as per your question, I just think out this code which is the same with SUMIF function in excel. Having another way is using grouping and merge table as below and this is faster then.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"Date"}, {{"Total", each List.Sum([Time]), type number}}),
        Merge = Table.NestedJoin(Source,"Date",Group,"Date","NewColumn"),
        Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"Total"}, {"Total"})
    in
        Expand

    Regards,

    • Marked as answer by Qilong L Saturday, December 27, 2014 1:32 PM
    Friday, December 26, 2014 8:58 AM

All replies

  • Hi,

    as your sample, You can use below code :

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        FnSumif = (Create) => List.Sum( Table.SelectRows(Source, each ([Date] = Create )) [Time] ),
        AddCol = Table.AddColumn( Source, "Total", each FnSumif( [Date] ) )
    in
        AddCol

    Regards,


    Thursday, December 25, 2014 9:30 AM
  • Hi,

    Thanks so much for your help.

    I tried your code and it worked. However, this method calculates even slower than that using the SUMIF in the worksheet.

    So I'm wondering is there any other ways we can improve the efficiency of the calculation?

    Sorry about the unreasonable question.

    Thanks again.

    Best regards,

    Friday, December 26, 2014 2:01 AM
  • hi,

    I tested this code with 10 thousands lines. The performance of this code is very bad. Actually, as per your question, I just think out this code which is the same with SUMIF function in excel. Having another way is using grouping and merge table as below and this is faster then.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"Date"}, {{"Total", each List.Sum([Time]), type number}}),
        Merge = Table.NestedJoin(Source,"Date",Group,"Date","NewColumn"),
        Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"Total"}, {"Total"})
    in
        Expand

    Regards,

    • Marked as answer by Qilong L Saturday, December 27, 2014 1:32 PM
    Friday, December 26, 2014 8:58 AM
  • Hi,

    Thanks so much for your kind help. The problem is perfectly solved.

    Best regards,

    Saturday, December 27, 2014 1:32 PM