Reply
jcastr02
Post Prodigy
Post Prodigy

Historical Log support

‎03-18-2025 07:54 AM

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

StorePCC SupportReporting 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
689TOD116/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
689TOD114/1/2024
689 5/1/2024
689 6/1/2024
689TOD117/1/2024
689TOD118/1/2024
689TOD119/1/2024
689TOD1110/1/2024
689TOD1111/1/2024
689TOD1112/1/2024
689TOD111/1/2025
689 2/1/2025
2928TOD1111/1/2022
2928TOD1112/1/2022
2928TOD111/1/2023
2928TOD112/1/2023
2928TOD113/1/2023
2928TOD114/1/2023
2928TOD115/1/2023
2928 6/1/2023
2928TOD117/1/2023
2928TOD118/1/2023
2928TOD119/1/2023
2928TOD1110/1/2023
2928TOD1111/1/2023
2928TOD111/1/2024
2928TOD112/1/2024
2928TOD113/1/2024
2928TOD114/1/2024
2928TOD115/1/2024
2928 6/1/2024
2928TOD97/1/2024
2928TOD98/1/2024
2928TOD99/1/2024
2928TOD910/1/2024
2928TOD911/1/2024
2928TOD912/1/2024
2928TOD91/1/2025
2928TOD92/1/2025

 

Expected result

StorePCC SupportStart End
689TOD116/1/20236/30/2023
689TOD114/1/20244/30/2024
689TOD117/1/20241/31/2025
2928TOD1111/1/20225/30/2023
2928TOD117/1/20235/30/2024
2928TOD97/1/2024 
Labels:
Message 1 of 6
185 Views
0
1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

‎03-18-2025 11:05 AM

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"

ronrsnfld_0-1742321109346.png

 

 

View solution in original post

Message 4 of 6
131 Views
5 REPLIES 5
ronrsnfld
Super User
Super User

‎03-18-2025 11:05 AM

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"

ronrsnfld_0-1742321109346.png

 

 

Message 4 of 6
132 Views
jcastr02
Post Prodigy
Post Prodigy
In response to ronrsnfld

Friday

Thank you @ronrsnfld 

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

jcastr02_0-1743185426603.png
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"

Message 5 of 6
40 Views
0
ronrsnfld
Super User
Super User
In response to jcastr02

Friday

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.

Message 6 of 6
33 Views
0
bhanu_gautam
Super User
Super User

‎03-18-2025 08:31 AM

@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!


LinkedIn






Message 2 of 6
159 Views
0
jcastr02
Post Prodigy
Post Prodigy
In response to bhanu_gautam

‎03-18-2025 10:16 AM

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?

Screenshot 2025-03-18 131539.png

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

Message 3 of 6
146 Views
0
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Stop
View All
Recommendations
Top Solution Authors (Last Month)
View All
Top Kudoed Authors (Last Month)
View All