Solved: Re: Historical Log support

jcastr02 · ‎03-18-2025

Hello I have a list of stores and respective different types of support provided along with the reporting month when that support was provided. I need to create a historical log of when support started and ended. I tried to group by but it didn't account for some of the breaks in support (where a store didn't' recieve support). It should also account for that if its the LAST reporting month shown then it should show blank for end date if there a support type listed (as example below store 2928 was)

Current

Store	PCC Support	Reporting Month
689		11/1/2022
689		12/1/2022
689		1/1/2023
689		2/1/2023
689		3/1/2023
689		4/1/2023
689		5/1/2023
689	TOD11	6/1/2023
689		7/1/2023
689		8/1/2023
689		9/1/2023
689		10/1/2023
689		11/1/2023
689		1/1/2024
689		2/1/2024
689		3/1/2024
689	TOD11	4/1/2024
689		5/1/2024
689		6/1/2024
689	TOD11	7/1/2024
689	TOD11	8/1/2024
689	TOD11	9/1/2024
689	TOD11	10/1/2024
689	TOD11	11/1/2024
689	TOD11	12/1/2024
689	TOD11	1/1/2025
689		2/1/2025
2928	TOD11	11/1/2022
2928	TOD11	12/1/2022
2928	TOD11	1/1/2023
2928	TOD11	2/1/2023
2928	TOD11	3/1/2023
2928	TOD11	4/1/2023
2928	TOD11	5/1/2023
2928		6/1/2023
2928	TOD11	7/1/2023
2928	TOD11	8/1/2023
2928	TOD11	9/1/2023
2928	TOD11	10/1/2023
2928	TOD11	11/1/2023
2928	TOD11	1/1/2024
2928	TOD11	2/1/2024
2928	TOD11	3/1/2024
2928	TOD11	4/1/2024
2928	TOD11	5/1/2024
2928		6/1/2024
2928	TOD9	7/1/2024
2928	TOD9	8/1/2024
2928	TOD9	9/1/2024
2928	TOD9	10/1/2024
2928	TOD9	11/1/2024
2928	TOD9	12/1/2024
2928	TOD9	1/1/2025
2928	TOD9	2/1/2025

Expected result

Store	PCC Support	Start	End
689	TOD11	6/1/2023	6/30/2023
689	TOD11	4/1/2024	4/30/2024
689	TOD11	7/1/2024	1/31/2025
2928	TOD11	11/1/2022	5/30/2023
2928	TOD11	7/1/2023	5/30/2024
2928	TOD9	7/1/2024

ronrsnfld · ‎03-18-2025

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"

View solution in original post

ronrsnfld · ‎03-18-2025

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"

jcastr02 · ‎03-28-2025

Thank you @ronrsnfld

Oddly when I filter for one store # and put the store filter = 2387 AFTER the grouping I get below...

When I put the store filter step BEFORE the grouping, then I get the same results you get.
Any recommendations on my Advanced Editor below?

let
Source = Excel.Workbook(Web.Contents("https://walgreens.sharepoint.com/sites/PhLEX/Shared%20Documents/General/PhLEX_PCC%20Analysis/DE_DR_P..."), null, true),
#"PhLEX_PCC-Archive_Sheet" = Source{[Item="PhLEX_PCC-Archive",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"PhLEX_PCC-Archive_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Store", Int64.Type}, {"Region#", Int64.Type}, {"Region Name", type text}, {"Area#", Int64.Type}, {"Area Name", type text}, {"District#", Int64.Type}, {"District Name", type text}, {"Physical Address", type text}, {"Physical Address City", type text}, {"Physical Address State/ Province", type text}, {"Physical Address Zip", type text}, {"Store Type", type text}, {"Store Design Type", type text}, {"Pharmacy", type text}, {"Location Status Date", type text}, {"Status Date", type date}, {"Tier - RX", type text}, {"DE Allowed", type text}, {"DR Allowed", type text}, {"PCC Allowed", type text}, {"PGI Status", type text}, {"HTS", type text}, {"Current MFC Support", type text}, {"Nucleus Ambient Launch Date", type date}, {"Telepharmacy", type text}, {"DE/F1 Support", type text}, {"DR/F4 Support", type text}, {"PCC Support", type text}, {"DE Usage % (Last 6 Weeks)", Int64.Type}, {"CR Usage % (Last 6 Weeks)", Int64.Type}, {"DR Usage % (Last 6 Weeks)", Int64.Type}, {"VbPT% (Last 6 Weeks)", Int64.Type}, {"Forecast Avg. Rx Vol. - Fall 22", type number}, {"Forecast Avg. Rx Vol - Fall 23", type number}, {"Forecast Avg. Rx Vol. - Winter 22", type number}, {"Forecast Avg. Rx Vol. - Winter 23", type number}, {"Forecast Avg. Rx Vol. - Spring 23", type number}, {"Financial Store", type text}, {"DE/F1 Base", type text}, {"DE/F1 HTS", Percentage.Type}, {"DE/F1 Patient Experience ", Percentage.Type}, {"DE Special Projects and Temp Support", type text}, {"DR/F4 Base", type text}, {"DR/F4 HTS", Percentage.Type}, {"DR/F4 Patient Experience ", Percentage.Type}, {"DR Special Projects and Temp Support", type text}, {"PCC Base", type text}, {"PCC Patient Experience", type text}, {"PCC Special Projects and Temp Support", type text}, {"ASA - PCC (Last 6 Weeks)", Int64.Type}, {"ASA - Store (Last 6 Weeks)", Int64.Type}, {"Temp. Support Notes", type text}, {"Temp. Support Start Date", type date}, {"Temp. Support End Date", type date}, {"Avg. Flu Vol. - Winter 22", type number}, {"AVg. Flu Vol. - Fall 23", type number}, {"Avg. Flu Vol. - Winter 23", type number}, {"Budget Hours % RPh (Last 6 Weeks)", Int64.Type}, {"Budge Hours % Tech (Last 6 Weeks)", Int64.Type}, {"DE Opt in Rate (Last 6 Weeks)", Int64.Type}, {"DR Opt in Rate (Last 6 Weeks)", Int64.Type}, {"Patient Exp. - Category", type text}, {"Patient Exp. - Delayed Rx Sold Tier", type text}, {"Patient Exp. - OOS Tier", type text}, {"Patient Exp. - Hold Time Tier", type text}, {"Patient Exp. - Promise Time Tier", type text}, {"Patient Exp. - WCB Tier", type text}, {"RVP", type text}, {"RHD", type text}, {"HCS", type text}, {"DM", type text}, {"Reporting Month", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Store", "DE/F1 Support", "Reporting Month"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"DE/F1 Support", "Support"}}),
#"Replaced Value3" = Table.ReplaceValue(#"Renamed Columns","0.85","85%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value3","0.15","15%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","0.6","60%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value2","0.45","45%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","0.3","30%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","1","100%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value" = Table.ReplaceValue(#"Replaced Value6","0.75","75%",Replacer.ReplaceValue,{"Support"}),
#"Replaced Value1 - Replace Null" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Support"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value1 - Replace Null", each ([Support] <> "")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Store", "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),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Support Type", each "DE"),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each [Store] = 2387)
in
#"Filtered Rows1"

ronrsnfld · ‎03-28-2025

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.

jcastr02 · ‎04-14-2025

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

ronrsnfld · ‎04-14-2025

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.

bhanu_gautam · ‎03-18-2025

@jcastr02 Try using below code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SortedRows = Table.Sort(Source,{{"Store", Order.Ascending}, {"Reporting Month", Order.Ascending}}),
AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 1, 1, Int64.Type),
FilledDown = Table.FillDown(AddedIndex,{"PCC Support"}),
AddedCustom = Table.AddColumn(FilledDown, "Support Change", each if [PCC Support] = null then null else if [Index] = 1 then 1 else if [PCC Support] <> Table.PreviousRow([PCC Support]) then 1 else null),
GroupedRows = Table.Group(AddedCustom, {"Store", "PCC Support", "Support Change"}, {{"Start", each List.Min([Reporting Month]), type date}, {"End", each List.Max([Reporting Month]), type date}}),
RemovedColumns = Table.RemoveColumns(GroupedRows,{"Support Change"}),
AdjustedEnd = Table.TransformColumns(RemovedColumns, {{"End", each if _ = List.Max(RemovedColumns[End]) then null else _, type nullable date}})
in
AdjustedEnd

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

jcastr02 · ‎03-18-2025

Thank you @bhanu_gautam I tried to populate Table.PreviousRow but it does not come up, do you know what I may be doing incorrect?

let
Source = Excel.Workbook(Web.Contents("https://walgreens-my.sharepoint.com/personal/joel_castro_walgreens_com/Documents/Desktop/Book2.xlsx"), null, true),
#"Sheet1 (2)_Sheet" = Source{[Item="Sheet1 (2)",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet1 (2)_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Store", Int64.Type}, {"PCC Support", type text}, {"Reporting Month", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Store", Order.Ascending}, {"Reporting Month", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Filled Down" = Table.FillDown(#"Added Index",{"PCC Support"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "Support Change", each if [PCC Support] = null then null else if [Index] = 1 then 1 else if [PCC Support] <> Table.P([PCC Support]) then 1 else null),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Store", "PCC Support", "Support Change"}, {{"Start", each List.Min([Reporting Month]), type date}, {"End", each List.Max([Reporting Month]), type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Support Change"}),
#"Adjusted End" = Table.TransformColumns(#"RemovedColumns", {{"End", each if _ = List.Max(RemovedColumns[End]) then null else _, type nullable date}})
in
AdjustedEnd

Historical Log support

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - February 2026

FabCon Atlanta 2026

FabCon is coming to Atlanta

Historical Log support

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - February 2026

FabCon Atlanta 2026