Forum Discussion

jr3151006's avatar
jr3151006
Helper IV
4 years ago

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 ...
Show and Tell
jr3151006's avatar
jr3151006
Helper IV
4 years ago

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"
jr3151006's avatar
jr3151006
Helper IV
4 years ago

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.