cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fafhrd
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Then replace

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

with

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

 

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Community Champion
Community Champion

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"

 

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.

 

Then replace

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

with

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

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.