Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Verkom
Regular Visitor

Remove records based on previous records

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:

Verkom_1-1649415932803.png

 

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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

 

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"

 

View solution in original post

@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

View solution in original post

6 REPLIES 6
Verkom
Regular Visitor

Hi Ibendlin,

 

Thank you for your reply.

 

Sample data:

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

Customer a

 

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

 

Thank you!

Friendly greetings

Anonymous
Not applicable

 

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"

 

@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

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/22Customer a
feb/22Customer b
mrt/22Customer a
apr/22Customer a
mei/22Customer a
jul/22Customer a
aug/22Customer a
nov/22Customer a
jun/23Customer a
jul/23Customer a
aug/23Customer a
sep/23Customer a
nov/23Customer b
jun/24

Customer a

 

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

 

Thank you!

Friendly greetings

 
 

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
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors