Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

HELP urgently needed!

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...
Need Help
Show and Tell
Tutorial Requests
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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

     

     

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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