Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi.
I have an existing table with multipple columns and rows.
Column A | Column B | Column C | Column D |
2020 | Yes | 6 | XX |
2018 | No | 8 | TG |
2017 | No | 3 | YY |
2020 | Yes | 8 | WV |
I like to create a new table based on the existing table and a formula for a row value on Column A = List.Max [Column A].
Which means in above example only the rows with value 2020 from column A should be returned.
If the existing table gets updated and a higher value is present in Column A then the new table should reflect only those records.
I hope it's clear what I would like to achieve.
Column A | Column B | Column C | Column D |
2020 | Yes | 6 | XX |
2020 | Yes | 8 | WV |
Any ideas?
Solved! Go to Solution.
@Namoh ,
Here is one solution:
Go to power query and rename last step in list of steps to "LastStep".
Go to Add column > Custom Column > enter this formula: List.Max(LastStep[Column A])
Add column > conditional column:
Last step: filter this last colum to display only value 1.
Try this.
Go to Power Query Editor.
Duplicate your table and go to advance editor of this.
Paste this code.
Make sure to replace table and column names as appropriate.
let
MaxValue = List.Max(Table.Column(PrimaryTable, "Column A")),
#"Filtered Rows" = Table.SelectRows(PrimaryTable, each ([Column A] = MaxValue))
in
#"Filtered Rows"
Hope this helps.
Please mark it as solution if this serves the purpose.
Thanks
Try this.
Go to Power Query Editor.
Duplicate your table and go to advance editor of this.
Paste this code.
Make sure to replace table and column names as appropriate.
let
MaxValue = List.Max(Table.Column(PrimaryTable, "Column A")),
#"Filtered Rows" = Table.SelectRows(PrimaryTable, each ([Column A] = MaxValue))
in
#"Filtered Rows"
Hope this helps.
Please mark it as solution if this serves the purpose.
Thanks
First look, and this works perfectly!
Hi @Namoh ,
you can do it like this (see figure. New data are only reflected if you refresh the report:
Regards FrankAT
Thanks, maybe a stupid question (I'm still abeginner) but that looks to be on the DAX side.
I'm looking at the PQE side.
Should have mentioned this in my start post.
Or am I wrong?
How/where to add this new table via your way?
@Namoh ,
Here is one solution:
Go to power query and rename last step in list of steps to "LastStep".
Go to Add column > Custom Column > enter this formula: List.Max(LastStep[Column A])
Add column > conditional column:
Last step: filter this last colum to display only value 1.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |