Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Namoh
Post Partisan
Post Partisan

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

Hi.

 

I have an existing table with multipple columns and rows.

Column AColumn BColumn CColumn D
2020Yes6XX
2018No8TG
2017No3YY
2020Yes8WV

 

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 AColumn BColumn CColumn D
2020Yes6XX
2020Yes8WV

Any ideas?

2 ACCEPTED SOLUTIONS
nandic
Memorable Member
Memorable Member

@Namoh ,
Here is one solution:
Go to power query and rename last step in list of steps to "LastStep".
pq max.PNG

Go to Add column > Custom Column > enter this formula: List.Max(LastStep[Column A])

pq max 2.PNG

Add column > conditional column:

pq max 3.PNG

Last step: filter this last colum to display only value 1.


View solution in original post

Anonymous
Not applicable

@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 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@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 

First look, and this works perfectly!

FrankAT
Community Champion
Community Champion

Hi @Namoh ,

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

 

30-07-_2020_14-48-08.png

 

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?

nandic
Memorable Member
Memorable Member

@Namoh ,
Here is one solution:
Go to power query and rename last step in list of steps to "LastStep".
pq max.PNG

Go to Add column > Custom Column > enter this formula: List.Max(LastStep[Column A])

pq max 2.PNG

Add column > conditional column:

pq max 3.PNG

Last step: filter this last colum to display only value 1.


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.