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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
jr3151006
Helper IV
Helper IV

How to create/reference a source table in to a new one (filtered)? >>> GROUPBY & MAXDATE

Hi,

 

this a similar environment for the real project; as you can see on the image bellow, 'consultants' performs some jobs but each time with a different price. That table has many records.


We wanna create a new - filtered - table where the consultants 'name' will appear only one time with the lastest price based on the last date seen.

 

>>> Is that possible??

 

SourceTable.PNG

 

The expected results would be a new table with only 02 records as below:


############################
#                TABLE_FILTERED              #

############################
|       DATE       |     NAME    |    PRICE  | 
========================

| 01/01/2022  |     JOHN     |     200     |
------------------------------------------
| 12/01/2022  |    MARY     |     195     |

------------------------------------------

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @jr3151006 ,

 

Thanks for your reply. If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

jr3151006
Helper IV
Helper IV

Another improvement and knowledge!!!!

After watch this video: How to filter a table to show only most recent date by group in Power Query - YouTube, I found how to accomplish and it's done.

1) Create a new 'reference' table pointing to the 'table_source';

2) GroupBy;

3) Modify the PowerQuery code as instructed by the video.


.

Here is the final 'PowerQuery code' in the 'Advanced Editor':
##############################################

let
    Source = ConsultantsPrice,
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"MaxDate", each List.Max([Date]), type date}, {"All", each _, type table [Date=nullable date, Name=nullable text, Price=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Price"}, {"All.Date", "All.Price"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([MaxDate]=[All.Date])),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"All.Date", "Name", "MaxDate", "All.Price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"MaxDate"})
in
    #"Removed Columns"

I would like to advice to be carefull with some things observed here:

 

1) Since the project already was running, after apply that settings on the main table, the other collumns receive the 'All' prefix in the name;

2) Now that some collumns changed the name, the MEASURES stoped to work and some charts stoped to work due to pointing to a 'wrong' field;

3) Relationship was affect.

 

I had to review the collumn names in order to remove the prefix 'All.' and also check relationship.

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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