cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saranp780
Helper III
Helper III

How to search the last row of the group and put the text or number?

I have some data like this.

 

The room (number)YearGrade
10220162.07
10220173
10220182.96
10320162.96
10320153.12
10320163.12
10320173.12
10420171.69
10420181.69
10520142.41
10520153.96
10520173.24

 

My requirement is to search the last row of every room number to put some remark in a new column.

Example

Room(number)YearGraderemark
10220162.07 
10220173 
10220182.96Last year of this room
10320162.96 
10320153.12 
10320163.12 
10320173.12Last year of this room
10420171.69 
10420181.69Last year of this room
10520142.41 
10520153.96 
10520173.24Last year of this room

The new column is to remark the last year of the room. So now, what do DAX or measure I have to use?

 

Thank you so much for giving help.

1 ACCEPTED SOLUTION
ValentinBIA
Resolver I
Resolver I

Hi @saranp780,

 

In order to ger what you are looking for, you should:

- Go to the Power Query Editor

- Duplicate your Query

- On the duplicated query, group by room number, with the operation max, on the column Year

- Add a custom column (= "Last year of this room")

- Go back to your main Query

- Merge with the duplicated query using the "the room number" as a 1st matching column and "year" as the 2nd matching column (hold control or shift when selecting to have more than one)

- Expand with only the custom column

- There you go !

 

Hope that helps !

 

Best,

 

Valentin

View solution in original post

1 REPLY 1
ValentinBIA
Resolver I
Resolver I

Hi @saranp780,

 

In order to ger what you are looking for, you should:

- Go to the Power Query Editor

- Duplicate your Query

- On the duplicated query, group by room number, with the operation max, on the column Year

- Add a custom column (= "Last year of this room")

- Go back to your main Query

- Merge with the duplicated query using the "the room number" as a 1st matching column and "year" as the 2nd matching column (hold control or shift when selecting to have more than one)

- Expand with only the custom column

- There you go !

 

Hope that helps !

 

Best,

 

Valentin

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.