Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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":
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |