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.

 

SalesIDStartDate
119/1/2000
22/3/2000
331/12/2000

 

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

TransIDSalesID CompleteDate
1121/1/2000
2131/1/2000
314/3/2000
4212/10/2000
5214/10/2000
635/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?

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

     

3 Replies

  • 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