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
Icon for Most Valuable Professional rankMost 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"