Filtering the table column resulting from a merge

[fafhrd]

I have Table 1 which is just a key column and a date column.

SalesID	StartDate
1	19/1/2000
2	2/3/2000
3	31/12/2000

And Table 2 which has some transactions and completion dates relating to Table 1:

TransID	SalesID	CompleteDate
1	1	21/1/2000
2	1	31/1/2000
3	1	4/3/2000
4	2	12/10/2000
5	2	14/10/2000
6	3	5/1/2001

I merge Table 1 and Table 2 resulting in a table column being added to Table 1 containing the related transactions.

I want to filter the table column to just the transactions that have been completed within 10 days from the start date of the corresponding sale. So that when I expand the table column, it will just be those ones.

How do I achieve this in Power Query?

[Vijay_A_Verma]

Best approach is expand and then filter. 

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN7TUNzIwMFCK1YlWMgKKGOsbIQSMQUqM9I0NoUKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesID = _t, StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"StartDate", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"SalesID"}, Table2, {"SalesID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"TransID", "CompleteDate"}, {"TransID", "CompleteDate"}),
    Custom1 = Table.SelectRows(#"Expanded Table2", each Duration.Days([CompleteDate]-[StartDate])<=10)
in
    Custom1

Code for Table2 (if needed)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TczZCQAgDAPQXfIt9FKXKd1/DU+00Pw8krpDUE5I5zEzojj0qSW1q0b1Y52w60yin1vi1O5rvh+3hYKIAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TransID = _t, SalesID = _t, CompleteDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransID", Int64.Type}, {"SalesID", Int64.Type}, {"CompleteDate", type date}})
in
    #"Changed Type"

But if you need the code where you want to do it before the expansion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN7TUNzIwMFCK1YlWMgKKGOsbIQSMQUqM9I0NoUKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesID = _t, StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}, {"StartDate", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"SalesID"}, Table2, {"SalesID"}, "Table2", JoinKind.LeftOuter),
    //Function Start
    fxProcess=(Tbl,Start_Date)=>
        let
            Custom2 = Table.SelectRows(Tbl, each Duration.Days([CompleteDate]-Start_Date)<=10),
            #"Removed Columns" = Table.RemoveColumns(Custom2,{"SalesID"})
        in
    #"Removed Columns",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each fxProcess([Table2],[StartDate])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table2"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"TransID", "CompleteDate"}, {"TransID", "CompleteDate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([TransID] <> null))
in
    #"Filtered Rows"

[fafhrd]

Thanks for the reply.

This solution is functionally equivalent to creating a custom column with definition:

Duration.Days([CompleteDate]-[StartDate])

and then filtering where that column is <= 10.

It doesn't work when there are transactions from previous years relating to the sale because they will have a negative duration and are thus < 10 as well.

I guess the correct way to describe my problem is that for each "sale" I wany to filter them by testing for the existence of a corresponding transaction in the transaction table that occured within 10 days of the completion of the sale. I wanto see a flag which shows which sales met the KPI (transaction within 10 days) and those that did not.

In SQL this is easy, I would use a where exists clause and write a sub query that returns transactions within 10 days of the outer query row. But I am stumped how to replicate such a thing in Power Query.

[Vijay_A_Verma · Accepted solution]

Then replace

Duration.Days([CompleteDate]-Start_Date)<=10

with

Duration.Days([CompleteDate]-[StartDate])>=0 and Duration.Days([CompleteDate]-[StartDate])<=10