Power Query - How to Find out items which have been appeared in my list for consecutive months

[khaled770]

I would like to find out which items have been appeared in my list for consecutive months , including the most recent month ( Jun ) , For instance, if my items were appeared  in June and May , it should give me 2. If they were in May, April ( Out of Final report as it is not including recent month ), it. If they were in June ,May, April and Marchit should give me 4. If they were in June ,May and  February (with April , March are  missing), it should give me 2

Output is about a unique list of items which have been appeared for consecutive months  with number of repeated time 

 Thanks in advance 

Data is 

output should be 

[ManuelBolz]

Hello @khaled770,

This isn't the best solution I've ever developed - but it works.

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KzEtPVVDSUfIqzQPSsTrRSo4FBTloQgGpeSh8z7xsFH5wfk5iEYqIMxo/JL+yGEUAYbNvYiWqvTABiK0wHsROGA+h3bGgCFU7TACiHcZDtq8I3b4iFPuKkHwA40HdD+PCPIzqOLBdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
    DatesToNumber = Table.AddColumn(Quelle, "MonthNumber", each if [Date] = "Januar" then 1
    else if [Date] = "Feb" then 2
    else if [Date] = "Mar" then 3
    else if [Date] = "Apr" then 4
    else if [Date] = "May" then 5
    else if [Date] = "June " then 6
    else if [Date] = "Juli" then 7
    else if [Date] = "August" then 8
    else if [Date] = "Sep" then 9
    else if [Date] = "Oct" then 10
    else if [Date] = "Nov" then 11
    else if [Date] = "Dec" then 12
    else null, Int64.Type),
    Sort = Table.Sort(DatesToNumber,{{"Item", Order.Ascending}, {"MonthNumber", Order.Ascending}}),
    TableBuffer = Table.Buffer(Sort),
    Index = Table.AddIndexColumn(TableBuffer, "Index", 1, 1, Int64.Type),
    Transform = Table.RemoveColumns(Table.TransformColumns(Index, {{"Index", each _ - 1, Int64.Type}}), {"Date"}),
    Rename = Table.RenameColumns(Transform,{{"Item", "JoinItem"}, {"MonthNumber", "JoinMonthNumber"}, {"Index", "JoinIndex"}}),
    Join = Table.Join(Index, "Index", Rename, "JoinIndex", JoinKind.LeftOuter),
    Differance = Table.AddColumn(Join, "Differance", each if [Item] = [JoinItem]
    then [JoinMonthNumber] - [MonthNumber]
    else 0, Int64.Type),
    Rows = Table.SelectRows(Differance, each ([Differance] = 1)),
    Group = Table.Group(Rows, {"Item"}, {{"Result", each Table.RowCount(_), Int64.Type}}),
    Additionsspalte = Table.TransformColumns(Group, {{"Result", each _ + 1, type number}}),
    SortResult = Table.Sort(Additionsspalte,{{"Result", Order.Descending}})
in
    SortResult

Best regards from Germany

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[khaled770]

I appreciate your assistance sir . One thing to note is that, in cases where items are duplicated in May and April but do not appear in the most recent month (June), I would like to filter them out.

[ManuelBolz]

Hello @khaled770,

I implemented it like this in my solution? Can you possibly provide further test data so that I can adapt the code? I get the following result:

[khaled770]

Thank you sir for your responses , 
I have removed Orange in Jun from my data set but appearantly it still show up in result ( based on May , Apr and Mar despite of it is not related to current month " Jun"

Data Set after remving Orange from Jun 

[AlienSx]

@khaled770 what about type of values in column Date? Is it text? 

[khaled770]

Its Name of the month , Type is date 

[AlienSx]

let
    Source = your_table,
    count = (lst, i) => 
        if i = List.Count(lst) or Date.AddMonths(Date.StartOfMonth(lst{i}), 1) <> Date.StartOfMonth(lst{i - 1}) 
        then i
        else @Anonymous(lst, i + 1),
    group = Table.Group(Source, "Items", {"Result", (x) => count(List.Sort(x[Date], Order.Descending), 1)})
in
    group