Sand
Sand

Reputation: 1

PowerBI GROUPBY date AND identifier

I am new to PowerBI and I am trying to group by the max date within the quarter, and then by unique identifier. Is this possible? My dataset looks like:

Date CompanyID Sales
3/31/2018 1 100
3/31/2018 2 200
3/31/2018 3 100
6/30/2018 2 300
3/31/2018 4 100
2/28/2018 4 75
1/31/2018 4 50
6/30/2018 4 200

I'm hoping to get:

Date CompanyID Sales
3/31/2018 1 100
3/31/2018 2 200
3/31/2018 3 100
3/31/2018 4 100
6/30/2018 2 300
6/30/2018 4 200

Appreciate any help here! Thank you!

Upvotes: 0

Views: 191

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

In Power Query (Home=>Transform)

  • Add extra columns for Quarter and Year
  • Group by ID / Quarter / Year
  • Extract the maximum date from each subtable, along with the associated Sales number
let

//Change code in next line to reflect however you are reading in your data,
// or refer to the table you already have
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CompanyID", Int64.Type}, {"Sales", Currency.Type}}),

//add custom columns for Quarter and Year
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Quarter", each Date.QuarterOfYear([Date]),Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([Date])),

//Group by ID / Quarter / Year
//Then return the last date for each subtable, and the corresponding Sales figure
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"CompanyID", "Quarter", "Year"}, {
        {"Date", each List.Max([Date]), type date},
        {"Sales", (t)=>Table.SelectRows(t, each [Date] = List.Max(t[Date]))[Sales]{0}, Currency.Type}
       }),

//Remove the Quarter and Year Column
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Quarter", "Year"}),

//Set the desired order
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "CompanyID", "Sales"}),

//Sort by Date and Company ID Ascending
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{
        {"Date", Order.Ascending},
        {"CompanyID", Order.Ascending}
        })
in
    #"Sorted Rows"

enter image description here

Upvotes: 1

Related Questions