Forum Discussion

jr3151006's avatar
jr3151006
Icon for Helper IV rankHelper 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 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??

 

 

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     |

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

Show and Tell

3 Replies

  • jr3151006's avatar
    jr3151006
    Icon for Helper IV rankHelper 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
      Icon for Helper IV rankHelper 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.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    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