Forum Discussion
Solved
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 P...
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
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