Forum Discussion

Ronald123's avatar
Ronald123
Resolver III
7 years ago
Solved

Matching transactions

Hello,   Can someone help me with the follow analyse? I need to find matching rows, the conditions are the follow. Date       > equal Product  > equal Receiver > equal TAG       > equal Tim...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Ronald123 

     

    Try this Custom Column

     

    Please takle a look at attached file

     

    =let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in
List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index])
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Hi Ronald123 

     

    My apologies for not getting back earlier

     

    Try this DAX column. It works with sample data.

    See if it performs better than M custom column

     

    Calculated Column =
CALCULATE (
    MIN ( Test[Index] ),
    FILTER (
        Test,
        [Product] = EARLIER ( [Product] )
            && [Receiver] = EARLIER ( [Receiver] )
            && [Date] = EARLIER ( [Date] )
            && [TAG] = EARLIER ( [TAG] )
            && ABS ( [TIme] - EARLIER ( [TIme] ) )
                <= TIME ( 0, 2, 0 )
    )
)
Ronald123's avatar
Ronald123
Resolver III
7 years ago

Zubair_Muhammad ,

 

Thx for the reply either i'm missing some calculating conditions;

Date must be equal
Tag must be equal

Time - time frame of 2 minutes

 

Greets,

 

Ronald

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Ronald123 

 

My bad  :smileyembarrassed: I didn't look at your question carefully.

Just looked at the picture.

 

Will get back to you

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Ronald123 

     

    Try this Custom Column

     

    Please takle a look at attached file

     

    =let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in
List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index])
    • Ronald123's avatar
      Ronald123
      Resolver III
      7 years ago

      Zubair_Muhammad ,

       

      I use the Power Query you've made in a calculation for a Google Big Query project.
      The Google Big Query is created with a SQL with a max of 100.000 lines.

      Either the Power Query calculation calculate over the more than 20 milion lines.

       

      Also checked with a CSV with 100.000 lines with a total of 10 mb.
      The query is running a quarter with a total of 12 GB loaded in the model.

       

       

      Is there a solution for this issue?

       

      Greets,

       

      Ronald 

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        Ronald123 

         

        Try Table.Buffer,

         

        IN the Query Editor>>View>>advanced editor

         

        In the step just before the step in which custom column was added, try wrapping the code inside Table.Buffer

        For example in the file I shared

         

            #"Changed Type1" = Table.Buffer(Table.TransformColumnTypes(#"Duplicated Column",{{"TIme2", type number}})),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in
List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index]))
in
    #"Added Custom1"