Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago
Solved

Group By & logic for historical view

I have a query with three columns  Store, Support Type, and Reporting Month.  I'd like to be able to show a historical time view of the support type by store and shrink the list when the support type stays the same month to month.  I am basically saying that store 162 had support type "Full" from 11/1/2022 thru 5/31/2024 (since there is nothing listed in 6/1/2024 for that store).  Then resumed again starting on 7/1/2024 thru 9/30/2024.  Note that support type full started again on 2/1/2025 but has no end date because the next month (3/1/2025) is not reported out on yet, thus it should stay blank until a different support type shows up next month or the entry under the support type is blank.  I am hoping that makes sense.  I added below a list of current and expected results.  How can I achieve this in power query.

Current

StoreSupport TypeReporting Month
162Full11/1/2022
162Full12/1/2022
162Full1/1/2023
162Full2/1/2023
162Full3/1/2023
162Full4/1/2023
162Full5/1/2023
162Full6/1/2023
162Full7/1/2023
162Full8/1/2023
162Full9/1/2023
162Full10/1/2023
162Full11/1/2023
162Full1/1/2024
162Full2/1/2024
162Full3/1/2024
162Full4/1/2024
162Full5/1/2024
162 6/1/2024
162Full7/1/2024
162Full8/1/2024
162Full9/1/2024
162TOD910/1/2024
162TOD911/1/2024
162TOD912/1/2024
162TOD91/1/2025
162Full2/1/2025
178TOD98/1/2024
178TOD99/1/2024
178 10/1/2024
178TOD911/1/2024
178TOD912/1/2024
178TOD91/1/2025
178TOD9

2/1/2025

 

Expected

StoreSupport TypeStart DateEnd Date
162Full11/1/20225/31/2024
162Full7/1/20249/30/2024
162TOD910/1/20241/31/2025
162Full2/1/2025 
178TOD98/1/20249/30/2024
178TOD911/1/2024 
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    read about GroupKind.Local

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxCoQwEEbhq0hqQTNJTNIvtttsJ97A1vu7oJJo5lVTfAz8vGUxdhLTm3nftv+xdrCDjCJm7d8kTKe4VgTFoXiUgDKhRJSEklHsyMQVLvHYRxGH4lFCK12JozxElISSW/l9P7mKo5GS4CalwU2nBOx2SUzl5zm8ltxK166uP16rHyRMuK2sXg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"Support Type" = _t, #"Reporting Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting Month", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store", "Support Type"}, {{"Start Date", each List.Min([Reporting Month]), type nullable date}, {"End Date", each List.Max([Reporting Month]), type nullable date}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Support Type] <> " "))
in
    #"Filtered Rows"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    read about GroupKind.Local

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxCoQwEEbhq0hqQTNJTNIvtttsJ97A1vu7oJJo5lVTfAz8vGUxdhLTm3nftv+xdrCDjCJm7d8kTKe4VgTFoXiUgDKhRJSEklHsyMQVLvHYRxGH4lFCK12JozxElISSW/l9P7mKo5GS4CalwU2nBOx2SUzl5zm8ltxK166uP16rHyRMuK2sXg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"Support Type" = _t, #"Reporting Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting Month", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store", "Support Type"}, {{"Start Date", each List.Min([Reporting Month]), type nullable date}, {"End Date", each List.Max([Reporting Month]), type nullable date}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Support Type] <> " "))
in
    #"Filtered Rows"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

  • ZhangKun's avatar
    ZhangKun
    Super User
    1 year ago

    The last value of the End Date column shouldn't appear?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxCoQwEEbhq0hqQTNJTNIvtttsJ97A1vu7oJJo5lVTfAz8vGUxdhLTm3nftv+xdrCDjCJm7d8kTKe4VgTFoXiUgDKhRJSEklHsyMQVLvHYRxGH4lFCK12JozxElISSW/l9P7mKo5GS4CalwU2nBOx2SUzl5zm8ltxK166uP16rHyRMuK2sXg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"Support Type" = _t, #"Reporting Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Support Type", type text}, {"Reporting Month", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Store", "Support Type"}, 
            {
                {"Start Date", each List.Min([Reporting Month])}, 
                {"End Date", each if Table.RowCount(_) = 1 then null else Date.EndOfMonth(List.Max([Reporting Month]))}
            }, 
            GroupKind.Local
        ),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Support Type] <> " "))
in
    #"Filtered Rows"