HELP urgently needed!

Hi Anonymous, I've played with this query a bit 🙂

 

I don't know how many rows do you have, but this one should be faster than ronrsnfld.

 

You can choose between 4 output formats. In __FormatPreview__ step you can check options and below in __SelectFormat__ you have to write such version number i.e. 1

 

Output will be in selected format:

 

There is also possibility to change culture for month names but maybe next time 😉

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lEyNNI3NNc3MjAyAnL8C1LzgJSCUqwOQtrIEibtnJNfnJoCEtU3sgAJmiAUGusbGoOEjNHUGSKpA8mZ6BsawtShWgcSMdM3MsIuC7LfFGocih1EyhuC5Q0tsLnRAM2NIE/jdoWZvqElpmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Priority = _t, #"Date of Issue" = _t, Status = _t, #"Date of Finalized" = _t]),
    __FormatPreview__ = #table(type table[Version=Int64.Type, Format=text], {{1, "202401"}, {2, "2024-01"}, {3, "2024-Jan"}, {4, "Jan-2024"}}),
    __SelectFormat__ = 1,
    StepBack = Source,
    ChangedType = Table.TransformColumnTypes(StepBack,{
        {"Priority", type text}, {"Date of Issue", type date}, {"Status", type text}, {"Date of Finalized", type date}}, "en-US"),
    //If Status=Closed and Finalize = null make Finalize = Date of Issue
//May not need this if your data is clean
    ReplaceNullFinalized = Table.ReplaceValue( ChangedType,
        each [Date of Issue],
        each [Status],
        (final,issue, status)=> if status="Closed" and final=null then issue else final,
        {"Date of Finalized"} ),
    Ad_OpenedMonths = Table.AddColumn(ReplaceNullFinalized, "Opened Months", each 
        [ now = Date.Year(DateTime.LocalNow())*100 + Date.Month(DateTime.LocalNow()), //Format yyyyMM as number
          a1 = Date.Year([Date of Issue])*100 + Date.Month([Date of Issue]), //Format yyyyMM as number
          a2 = Date.Year([Date of Finalized])*100 + Date.Month([Date of Finalized]), //Format yyyyMM as number 
          b = List.Generate( ()=> a1, each _ <= now, each if Text.End(Text.From(_), 2) = "12" then _ +89 else _ +1 ), //Generate Months between [Date of Issue] and now, format yyyyMM as number
          c = if [Date of Finalized] = null then null else List.Generate( ()=> a1, each _ <= a2, each if Text.End(Text.From(_), 2) = "12" then _ +89 else _ +1 ), //Generate Months between [Date of Issue] and now, format yyyyMM as number
          d = if [Date of Finalized] = null then b else List.Intersect({ b, c }),
          e = if not List.Contains({1..4}, __SelectFormat__) then d else 
                   List.Transform(d, (x)=> 
                      if __SelectFormat__ = 2 then Text.Start(Text.From(x), 4) & "-" & Text.End(Text.From(x), 2)
                      else if __SelectFormat__ = 3 then Date.ToText(Date.FromText(Text.From(x*100+1), [Format="yyyyMMdd"]), "yyyy-MMM", "en-US")
                      else if __SelectFormat__ = 4 then Date.ToText(Date.FromText(Text.From(x*100+1), [Format="yyyyMMdd"]), "MMM-yyyy", "en-US") else x)
        ][e], type list),
    ExpandedOpenedMonths = Table.ExpandListColumn(Ad_OpenedMonths, "Opened Months"),
    GroupedRows = Table.Group(ExpandedOpenedMonths, {"Priority", "Opened Months"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    PivotedColumn = Table.Pivot(Table.TransformColumnTypes(GroupedRows, {{"Opened Months", type text}}, "sk-SK"), List.Distinct(Table.TransformColumnTypes(GroupedRows, {{"Opened Months", type text}}, "sk-SK")[#"Opened Months"]), "Opened Months", "Count")
in
    PivotedColumn

 

Hi Anonymous, From your sample data, do you consider 'Open' documents to be where 'Date of Finalized' is blank? If so, you have 7 open documents by 'Date of Issue' Year and Month. Is this correct? See the attached .pbix file.