Filter Merge step by another column

[Anonymous]

How can you filter a column from a "Merged" step by another field in the query? For example, filter each "SECOND"[DATES_SECOND] > [DATE_FIRST].

ID      DATE_FIRST

1	20/08/2020
2	20/09/2020
3	12/09/2020

ID      DATES_SECOND

1	19/08/2020
1	20/08/2020
1	21/08/2020
2	19/09/2020
2	20/09/2020
2	21/09/2020
3	11/09/2020
3	12/09/2020
3	13/09/2020

[ImkeF · Accepted solution]

Hi @Anonymous ,

for performance reasons, I'd strongly recommend to expand, then filter and re-group if necessary.

See this blogpost for more details: https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-query-and-power-bi/

[v-eqin-msft]

Hi @Anonymous ,

You could follow these steps:

1.Click Expand button ,select DATES_SECOND ( could named it)

2.Add a Conditional Column

3.Remove when the value =null

4.Delete the Custom column created in step2.

let
    Source = Table.NestedJoin(FirstTable, {"ID"}, SecondTable, {"ID"}, "SecondTable", JoinKind.LeftOuter),
    #"Expanded SecondTable" = Table.ExpandTableColumn(Source, "SecondTable", {"DATES_SECOND"}, {"SecondTable.DATES_SECOND"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded SecondTable",{{"SecondTable.DATES_SECOND", "DATES_SECOND"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [DATE_FIRST] < [DATES_SECOND] then [ID] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Custom] <> null and [Custom] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"

My final table looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @v-eqin-msft ,

thank you for the answer. Yes, I know, that´s what @ImkeF suggested in her first reply, but I´d like to learn to operate inside the merge column in case I need to do something more complex.

[v-eqin-msft]

Hi @Anonymous ,

When you have one or more columns that you’d like to add to another query, you merge the queries.

You could take a look at these documents  for further information .

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://www.powerbi-pro.com/en/power-bi-seven-types-of-table-joins/

Best regards,

Eyelyn Qin

[ImkeF · Accepted solution]

Hi @Anonymous ,

there has been a syntax error in my last reply. This works:

Table.SelectRows([SECOND], (row) => [DATE_FIRST] > row[DATES_SECOND] )

But also a variation to your function approach would work as well:

let
    Sort = Table.Sort([SECOND],{{"DATES_SECOND", Order.Descending}}),
    Add = Table.AddColumn(Sort, "Custom", (row) => [DATE_FIRST] > row[DATES_SECOND] )
in
    Add

See attached file.

[amitchandak]

@Anonymous , I think you have to merge first using expand. Create a calculated column and remove rows based on that

refer the old topic:https://community.powerbi.com/t5/Desktop/M-Power-Query-Removing-entire-rows-based-on-condition/td-p/367563

[ImkeF · Accepted solution]

Hi @Anonymous ,

for performance reasons, I'd strongly recommend to expand, then filter and re-group if necessary.

See this blogpost for more details: https://www.thebiccountant.com/2019/10/28/performance-tip-for-aggregations-after-joins-in-power-query-and-power-bi/

[Anonymous]

Hi @ImkeF , @amitchandak 

Thank you for your answer. Maybe I´m not explaining myself correctly, I don´t think aggregations have anything to do here?

Unfortunately, expanding, filtering and the grouping again would be a pain as there are a lot of columns, but I´ll try that and see how it goes thanks. What I´m struggling with is how to operate between the values of a colum after a merge, where every cell is indeed a table, and the rest of the first table. Maybe I want to add a custom column based on the values of "DATE_FIRST":

Maybe I want to filter DATES_SECOND by the value of DATE_FIRST as I said before.

I have been trying to do this with a function like:

(MyTable) =>

let
Sort = Table.Sort(MyTable,{{"DATES_SECOND", Order.Descending}}),
Add = Table.AddColumn(Sort, "Custom", each if "DATES_SECOND" > "DATE_FIRST" then 1 else 0),
Keep = Table.SelectRows(Add, each ([Custom] = 1))
in
Keep

But the result is empty, I think it can't "find" DATE_FIRST.

[ImkeF]

Hi @Anonymous ,

it was just a thought that expanding would be faster, as the operations are fairly similar.

If you want to go the other way, you have to add a column to your table where you have to declare at least some of your variables explitely and now use the "each"-syntax sugar twice. Because that makes it ambiguous:

Table.SelectColumns([SECOND], "Custom", (row) => if row([DATE_FIRST]) > [DATES_SECOND] )

so here the "row" is an explicit function variable that lets you specifically adress each row in the merged table.

[Anonymous]

Hi @ImkeF ,

I´ve made several attempts with the (row) syntax but have not gotten there yet:

[ImkeF]

Sorry @Anonymous ,

the "if" shouldn't be there. The expression has to return a boolean value that determines if the currently iterated row will be kept or not. So sth like this should get you going.

Otherwise please past link to file with sample data.

Table.SelectColumns([SECOND], "Custom", (row) => row([DATE_FIRST]) > [DATES_SECOND] )

Use it in your latter versions with the Table.AddColumn

[Anonymous]

Thank you for the help @ImkeF ,

here is the sample .pbix I´m using: https://1drv.ms/u/s!AiWqeJuU1-S3akALKVh42fxmfkk?e=nirahP

Using Table.SelectRows keeps giving me the Expression.Error "cannot convert Record to Function". 

Using this function:

(MyTable) =>
let
    Sort = Table.Sort(MyTable,{{"DATES_SECOND", Order.Descending}}),
    Add = Table.AddColumn(Sort, "Custom", (row) => row([DATE_FIRST]) > [DATES_SECOND] )
in
    Add

Also gives the Expression.Error "cannot convert Record to Function":