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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.