Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

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 8
6,660 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 8
6,606 Views
7 REPLIES 7
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 8
6,607 Views
jcastr02
Post Prodigy
Post Prodigy
In response to ronrsnfld

‎03-28-2025 11:12 AM

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 8
6,515 Views
0
ronrsnfld
Super User
Super User
In response to jcastr02

‎03-28-2025 12:55 PM

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 8
6,508 Views
0
jcastr02
Post Prodigy
Post Prodigy
In response to ronrsnfld

‎04-14-2025 08:18 AM

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...

StoreSupportReporting 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
238715%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
2387AutoPhLEX + On Demand10/1/2024
2387AutoPhLEX + On Demand11/1/2024
2387Y12/1/2024
2387Y1/1/2025
2387Y2/1/2025
2387Y3/1/2025
2387Y

4/1/2025

 

Message 7 of 8
6,456 Views
0
ronrsnfld
Super User
Super User
In response to jcastr02

‎04-14-2025 11:29 AM

I cannot reproduce your problem with the data source you supply.

Using your code, which has the filter after, I get:

ronrsnfld_0-1744654882251.png

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.

 

 

Message 8 of 8
6,447 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 8
6,634 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 8
6,621 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All