How to use Table slicers with Power Query Groupby

Question

So I have two tables one shows factories and daily volume by factory and Volumes for measure. The other is just a list of factories and measure points. The slicer is on the 2nd one. Both are loaded to power query.

I am trying to do use the table slicer to control a group-by month in power query. The slicer makes it so the columns end up changing. Code is below
let Source = #"Daily", CL = Table.ColumnNames(Source), Cm = List.Count(CL),![38926-daily.jpg][1]![38883-slicers.jpg][2] Cn = Cm-1, wt = CL{3}, // The below part does not work. The Idea behind CL{Cn} is to allow the list of columns to very based on slicer choice. #"Grouped Rows" = #!"Table.Group(Source, {""Mon-Yr""}, {{CL{Cn}, each List.Sum(Source[CL{Cn}]), type nullable number}})" in #"Grouped Rows"

I placed a copy of the excel file in the link below.

EbvAfTyOXLtGo2M4zQ7rBqYBAfgLyxSqjYJJ9A3NIlUukg

Answer 1

This is one solution that requires the use of unpivot then pivot again. I would like to see if someone comes up with different solution.

    let
    Source = #"Daily",
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Collection Date", "Eomonth"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Eomonth", "Attribute"}, {<!-- -->{"Value", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

This works because the table Slicer is converted to a list then that list is used for headers by the code = Table.SelectColumns(#"Changed Type", List.Combine({LeftColumns,Slicer})).

Answer 2

Hi @Shane Sibbett

If I understood what you want to acheive you're probably looking for something like this:

let  
    Source = #"Daily",  
    AggregList = List.Transform(Slicer,  
        (fn)=> {fn, each List.Sum(Record.Field(_, fn)), type nullable number}  
    ),  
    GroupedRows = Table.Group(Source, {"Eomonth"}, AggregList)  
in  
    GroupedRows