Forum Discussion

fafhrd's avatar
fafhrd
Frequent Visitor
4 years ago
Solved

Filtering the table column resulting from a merge

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 da...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Then replace

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

    with

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

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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's avatar
fafhrd
Frequent Visitor
4 years ago

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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Then replace

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

    with

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