Forum Discussion
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:
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
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
6 Replies
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/1447523Hi 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
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
