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"

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago

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