Reputation: 1
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
Reputation: 60224
In Power Query (Home=>Transform
)
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"
Upvotes: 1