Power Query Summary Table

[Anonymous]

Hello,

I'm trying to build a summary table in Power Query to build a waterfall chart. I'm planning to create a table with one column per category that will end up in the waterfall, then transpose the data so it ends up with the original headers as category names. I'm running into an error when trying to push the result of a List.Sum into the blank table. The lines which are throwing the error are: 

FilteredTable=Table.SelectRows(#"Full Backlog", each Date.Year([Customer Required Date])=2017),
    #"BL Waterfall" = #table({"Order Line"},List.Sum(Table.Column(FilteredTable,"Order Line")))

The "Order Line" column in "Full Backlog" is a list of numbers. When I try to add the summarized value to the BL Waterfall, it throws an error that "We cannot convert the value 1200 to type List." Any suggestions on how to correct this error, or how to create this summary table more efficiently? There will be about a dozen columns in the end summarizing data from 5 or so tables.

Thanks.

[Anonymous · Accepted solution]

Just found one way to solve this problem. This could be condensed, but this shows the steps separately:

    FilteredTable2017=Table.SelectRows(#"Full Backlog", each Date.Year([Customer Required Date])=2018 or Date.Year([Customer Required Date])=2017),
    #"BL Waterfall2017" = List.Sum(Table.Column(FilteredTable2017,"Order Line")),
    FilteredTable2018=Table.SelectRows(#"Full Backlog", each Date.Year([Customer Required Date])=2018),
    #"BL Waterfall2018" = List.Sum(Table.Column(FilteredTable2018,"Order Line")),
    #"Converted to List" = {[Backlog=#"BL Waterfall2017",Current=#"BL Waterfall2018"]},
    #"Converted to Table" = Record.ToTable(#"Converted to List"{0})