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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors