New table based on existing table and a formula for row value

[Namoh]

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?

[nandic · Accepted 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.

[Anonymous · Accepted solution]

@Namoh 

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 

[Namoh]

First look, and this works perfectly!

[FrankAT]

Hi @Namoh ,

you can do it like this (see figure. New data are only reflected if you refresh the report:

Regards FrankAT

[Namoh]

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?

[nandic · Accepted 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.