Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago
Solved

Historical Log support

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 star...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    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"

     

     

ronrsnfld's avatar
ronrsnfld
Super User
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"

 

 

  • jcastr02's avatar
    jcastr02
    Post Prodigy
    1 year ago

    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_PCC%20Support%20Trend.xlsx"), 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's avatar
      ronrsnfld
      Super User
      1 year ago

      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's avatar
        jcastr02
        Post Prodigy
        1 year ago

        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