HELP urgently needed!

[Anonymous]

Hi All,

Another day another challenge) Please help me if you faced someting similar

I've got some data where shown some documents priority, date of issue, date of finalized, and status.

What I need is to count how many open documents we have by each month, but here is when it gets tricky... 

For example: to count how many open docs I've got in February, I filter March of current year in column "date of issue", then go to column "date of finalized" and filter out all closed in current year February ones, but keep March (cause they weren't closed in February). How can I transform information like that into simplest outcome like Table #2 below

My data (only part of it just for example) in Table #1:

Tab #1

Priority	Date of Issue	Status	Date of Finalized
P1	12/17/2022	Open
P1	12/29/2022	Closed	1/28/2024
P1	3/13/2023	Closed	1/21/2024
P3	4/11/2023	Open
P3	6/22/2023	Open
P2	5/21/2023	Closed
P2	5/21/2023	Closed
P1	5/18/2023	Closed	1/20/2024
P3	12/2/2023	Open
P2	6/19/2023	Open

Tab #2

Priority	January	February	March	April etc.
P1	numbers	numbers	numbers	numbers
P2	numbers	numbers	numbers	numbers
P3	numbers	numbers	numbers	numbers

[dufoq3]

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

[amustafa]

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.

[Anonymous]

Hello @amustafa ! Thanks for your reply, it looks great btw) 

Yes, if Status is open, date finalized column must be blank. 

What I need is, while counting total of certain month, should be counted all open dates (without considering a year) in one but without that certain month closed ones. For example, I need to count  how many open documents I've got by February, so those documents that have closure date in February doesn't count, but those with March dates must be included cause they were open in February. So counting should go by Priorities, status and date of closure. 

[ronrsnfld]

What do you want done if an item is marked with Status:Closed but the Finalized column is blank?

[Anonymous]

that's impossible, cause main criteria is to put dates if status is closed

[ronrsnfld]

Row 6 & 7 of your sample data 

[Anonymous]

my bad) probably I just didn't put data in there

[ronrsnfld · Accepted solution]

Here is M-Code that should do what you want.

You may be able to omit the line that cleans the data where you have no Finalize date for Closed items (by replacing that null with the Issue date).

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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Priority", type text}, {"Date of Issue", type date}, {"Status", type text}, {"Date of Finalized", type date}}),

//If Status=Closed and Finalize = null make Finalize = Date of Issue
//May not need this if your data is clean
    #"Replace null Finalized" = Table.ReplaceValue(
        #"Changed Type",
        each [Date of Issue],
        each [Status],
        (final,issue, status)=> if status="Closed" and final=null then issue else final,
        {"Date of Finalized"}
    ),

//Task is "Open" in month that it is opened up to the month before closed or Month(Today) if not closed.
    #"Add Months Open" = Table.AddColumn(#"Replace null Finalized", "Months Open", (c)=>
        List.Generate(
            ()=>[m=Date.StartOfMonth(c[Date of Issue])],
            each if c[Status] = "Closed" 
                    then [m] < Date.StartOfMonth(c[Date of Finalized]) 
                    else [m] < Date.EndOfMonth(Date.From(DateTime.FixedLocalNow())),
            each [m = Date.AddMonths([m],1)],
            each [m]), type {date}
        ),
    #"All Months" = List.Transform(List.Sort(List.Distinct(List.Combine(#"Add Months Open"[Months Open])), Order.Ascending),
                        each Date.ToText(_, "MMM-yyyy")),
    #"Table Types" = 
        let 
            //columnNames = List.Transform(#"Removed Columns1"[all], each _{0}[Column1]),
            columnTypes = List.Repeat({Int64.Type}, List.Count(#"All Months")),
            rowColumnTypes = List.Transform(columnTypes, (t) => [Type = t, Optional = false]),
            rowType = Type.ForRecord(Record.FromList(rowColumnTypes, #"All Months"), false)
        in 
            rowType,

    #"Removed Columns" = Table.RemoveColumns(#"Add Months Open",{"Date of Issue", "Status", "Date of Finalized"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Priority"}, {
        {"Month Count", (t)=> let 
            #"Expand Date List" = Table.SelectRows(Table.ExpandListColumn(t,"Months Open"), each [Months Open] <> null),
            #"Format Dates" = Table.TransformColumns(#"Expand Date List", {"Months Open", each Date.ToText(_,"MMM-yyyy"), type text}),
            #"Pivoted Column" = Table.Pivot(#"Format Dates", 
                List.Distinct(#"Format Dates"[#"Months Open"]), "Months Open", "Priority", List.Count)
        in #"Pivoted Column", type table #"Table Types"
            }
        }),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Priority", Order.Ascending}}),
    #"Expanded Month Count" = Table.ExpandTableColumn(#"Sorted Rows", "Month Count", #"All Months")
in
    #"Expanded Month Count"

Data Sample:

Results

[Anonymous]

Thank you so much @ronrsnfld