Forum Discussion
Historical Log support
- 1 year ago
A different method, using GroupKind.Local:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZI7DoNADESvEm2NBPZ+3aenSYe4/zWCtIHsZ+wCIb0ngzye43CpiFvc63qIVlp5Y3bn0nFWeMV+wIyxxzhgHGf82d9E1zvhiYxxwVgwpk3hyq4/HHAEI/YzvpcKeCJinPQPZV0VXYmu7kigA/s/DoTwuKoijq1iFi7gVwwlW7I93eDYcN5wwXARuOZocCYbrhhODNd3WYkTy/ZyODHovOGC4SJwU83/MzLWvFdFV6KrvuaDUwKRqeaDw32WrurnFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"PCC Support" = _t, #"Reporting Month" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Store", Int64.Type}, {"PCC Support", type text}, {"Reporting Month", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Store","PCC Support"}, { {"Start", each List.Min([Reporting Month]), type nullable date}, //Assuming "Last reporting month" is the month preceding the current month {"End", each [a=Date.EndOfMonth(List.Max([Reporting Month])), b=Date.IsInPreviousNMonths(a,1), c=if b then null else a][c], type nullable date}}, GroupKind.Local), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([PCC Support] <> " ")) in #"Filtered Rows"
Probably something different about your real data c/w your sample data. But I can't really tell without the data, and I can't download your sharepoint file.
Hello ronrsnfld Would you be able to use below as datasource? When I filter for one specific store and put that step before the grouping it works as expected, but if I put it after, it sorta "undoes" the grouping...
| Store | Support | Reporting Month |
| 2387 | 12/1/2023 | |
| 2387 | 11/1/2022 | |
| 2387 | 12/1/2022 | |
| 2387 | 1/1/2023 | |
| 2387 | 2/1/2023 | |
| 2387 | 3/1/2023 | |
| 2387 | 4/1/2023 | |
| 2387 | 5/1/2023 | |
| 2387 | 15% | 6/1/2023 |
| 2387 | 7/1/2023 | |
| 2387 | 8/1/2023 | |
| 2387 | 9/1/2023 | |
| 2387 | 10/1/2023 | |
| 2387 | 11/1/2023 | |
| 2387 | 1/1/2024 | |
| 2387 | 2/1/2024 | |
| 2387 | 3/1/2024 | |
| 2387 | 4/1/2024 | |
| 2387 | 5/1/2024 | |
| 2387 | 6/1/2024 | |
| 2387 | 7/1/2024 | |
| 2387 | 8/1/2024 | |
| 2387 | 9/1/2024 | |
| 2387 | AutoPhLEX + On Demand | 10/1/2024 |
| 2387 | AutoPhLEX + On Demand | 11/1/2024 |
| 2387 | Y | 12/1/2024 |
| 2387 | Y | 1/1/2025 |
| 2387 | Y | 2/1/2025 |
| 2387 | Y | 3/1/2025 |
| 2387 | Y | 4/1/2025
|
- ronrsnfld1 year agoSuper User
I cannot reproduce your problem with the data source you supply.
Using your code, which has the filter after, I get:
which seems appropriate.
I notice you have some different column names "DE/F1 Support" ==>"Support" and also grouping on " {"Store", "Support"}" instead of {"Store", "PCC Support"} but I have no idea if that would make a difference.
Since I can't reproduce the problem here, I don't have any other thoughts.
If I were able to reproduce the problem, then maybe I could figure out what's going on.