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 )
    )
)
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"
Ronald123's avatar
Ronald123
Resolver III
7 years ago

Zubair_Muhammad ,

 

Tanks for you're reply but table.buffer doesn't help.
In Power Query is't possible to run the calculation but is isn't possible to apply the query in the model.

On my personal computer i have memory issue's with this calculation and the server of my work is runing  and runing...

Is there an another option? Maybe DAX?

 

Greets,

 

Ronald

  • Ronald123's avatar
    Ronald123
    Resolver III
    7 years ago

    Zubair_Muhammad ,


    When you have time to look at it?

    Don't feel any pressure :)

     

    Greets,

     

    Ronald

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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 )
    )
)