Remove records based on previous records

[Verkom]

Hi all,

For the last few days I've been breaking my head over this issue.

I can't find a proper solution on google, so I'm trying my luck here.

What I am trying to achieve w Power Query in PowerBI:

• Keep a record every >= 6months; remove all other records; start w first record
• The next record can be >1month than his previous record

Example:

So I would like to remove the red records. Every next green record is now atleast 6 months newer.

Is something even possible in Power Query?

Any help or advice would be highly appreciated!

Friendly greetings

[Anonymous · Accepted solution]

let
    mb = (d1, d2)=>
        let 
            months=(Date.Year(d1)-Date.Year(d2))*12+Date.Month(d1)-Date.Month(d2)
        in
    months ,
 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNUjAyUtJRSi4tVkhUitWJVvItqkIXciwowlCVmIku5FWagy6UWJqOLuSXX4apMU/ByBjTLFQhR5BZqELBqQWYGoFmmSCEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"month", type date}, {"customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"month"}, {{"all", each _}}, GroupKind.Local, (x,y)=>Number.From(mb(y[month],x[month])>=6))
in
    #"Grouped Rows"

[Verkom]

Hi Ibendlin,

Thank you for your reply.

Sample data:

feb/22	Customer a
mrt/22	Customer a
apr/22	Customer a
mei/22	Customer a
jul/22	Customer a
aug/22	Customer a
nov/22	Customer a
jun/23	Customer a
jul/23	Customer a
aug/23	Customer a
sep/23	Customer a
jun/24	Customer a

In this example I would like to have the bold text as the result.

Thank you!

Friendly greetings

[Anonymous · Accepted solution]

let
    mb = (d1, d2)=>
        let 
            months=(Date.Year(d1)-Date.Year(d2))*12+Date.Month(d1)-Date.Month(d2)
        in
    months ,
 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNUjAyUtJRSi4tVkhUitWJVvItqkIXciwowlCVmIku5FWagy6UWJqOLuSXX4apMU/ByBjTLFQhR5BZqELBqQWYGoFmmSCEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"month", type date}, {"customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"month"}, {{"all", each _}}, GroupKind.Local, (x,y)=>Number.From(mb(y[month],x[month])>=6))
in
    #"Grouped Rows"

[lbendlin · Accepted solution]

@Anonymous   nice - but please note that there are multiple years involved.  Your date conversion needs some work

let
    mb = (d1, d2) as number =>
        let 
            months=(Date.Year(d1)-Date.Year(d2))*12+Date.Month(d1)-Date.Month(d2)
        in
    months ,
 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNUjAyUtJRSi4tVkhUitWJVvJNLEIXcizAEPJNrEQX8irNQRdKLE1HF/LLL8PUmKdgZIxpFqqQI8gsVKHg1AJMjUCzTBBCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, customer = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," "," 20",Replacer.ReplaceText,{"month"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"month", type date}, {"customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"month"}, {}, GroupKind.Local, (x,y)=>Number.From(mb(y[month],x[month])>=6))
in
    #"Grouped Rows"

I was planning on playing with List.Generate but I think your approach is much more elegant.  Here's some background information on why:

Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant

[Verkom]

HI Ibendlin, Rocco_sprmnt21,

First of all, thank you so much for your answers. Highly appreciated.

Any chance you can explain me how I need to adjust the code above when I have multiple customers involved?

I've been reading about partial grouping and this 5th argument but I can't make it happen.

Example:

feb/22	Customer a
feb/22	Customer b
mrt/22	Customer a
apr/22	Customer a
mei/22	Customer a
jul/22	Customer a
aug/22	Customer a
nov/22	Customer a
jun/23	Customer a
jul/23	Customer a
aug/23	Customer a
sep/23	Customer a
nov/23	Customer b
jun/24	Customer a

I would like to have the bold text as the result.

Thank you!

Friendly greetings

[Verkom]

Ok, I found the solution to my above question. I added a sort and an extra condition to this 5th grouping argument.

let
    mb = (d1, d2) as number =>
        let 
            months=(Date.Year(d1)-Date.Year(d2))*12+Date.Month(d1)-Date.Month(d2)
        in
    months ,
 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSktN0jcyUtJRci4tLsnPTS1SSFSK1YlWykrMQxNPSgJL5BaVYNWQWFCEVTw3NRO7BaU52M0pTccqnpdfhsMcoEONcZiPRRxsPhbx4tQCksTB9poQEw7QgMP0MEgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, customer = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," "," 20",Replacer.ReplaceText,{"month"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"month", type date}, {"customer", type text}}),
    #"Row sorted" = Table.Sort(#"Changed Type",{{"customer", Order.Ascending}, {"month", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Row sorted", {"month","customer"}, {}, 0, (x,y)=>Number.From(mb(y[month],x[month])>=6 or x[customer] <> y[customer]))
in
    #"Grouped Rows"

Thanks for your help guys!

[lbendlin]

Definitely possible in Power Query. Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.


https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523