Forum Discussion

Verkom's avatar
Verkom
Regular Visitor
4 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

     

    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's avatar
    lbendlin
    4 years ago

    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's avatar
Verkom
Regular Visitor
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

 

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's avatar
    lbendlin
    Super User
    4 years ago

    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's avatar
      Verkom
      Regular Visitor
      4 years ago

      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

       
       
      • Verkom's avatar
        Verkom
        Regular Visitor
        4 years ago

        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!