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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Gabe_V
Helper I
Helper I

Copy down data based on max of another column

Hello!
I have spent almost a day and a half trying to get a column in my table to copy to all cells in that column based upon if they are associated with the most recent date in another column.

Essentially I have a table that looks like this:

Dataset DateProject IDPriorityStack Rank
11/1/2022ABC1233a51
10/1/2022ABC1233b48
12/1/2022ABC123237

 

What I would like is to look at the Dataset Date, find the most recent date (in this case 12/1/2022), and then fill the associated values in the Priority and Stack Rank columns (2 and 37 respectively in this case), to all rows in those columns, so the end result would look like:

Dataset DateProject IDPriorityStack Rank
11/1/2022ABC123237
10/1/2022ABC123237
12/1/2022ABC123237

I've had no luck with anything I've tried so far, and all the forums don't seem to cover this particular scenario.

Does anyone have any suggestions, or can at least point me in the right direction?

 

Cheers,

Gabe_V

1 ACCEPTED SOLUTION
latimeria
Solution Specialist
Solution Specialist

Hi @Gabe_V ,

 

You can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MjAyUtJRcnRyNjQyBjKME4GEqaFSrA5YgQGmgiQgYWIBU2CEoQDEMTaHySPbYGJqht8GqAKYDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dataset = _t, #"Project ID" = _t, #"ID priority" = _t, #"Stack Rank" = _t]),
    #"Grouped Rows" = Table.Group(Source, 
        {"Project ID"}, 
        {
            {"AllRows", 
            each 
                let
                    // get max value by group
                    Priority = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[ID priority]{0},
                    Stack = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[Stack Rank]{0},
                    // replaced values in the Group
                    ReplacedValue = Table.TransformColumns(
                        _,
                        {
                            { "ID priority",  (r)=> Priority },
                            { "Stack Rank",  (r)=> Stack }
                        }
                    )
                in 
                    ReplacedValue
            }
        }
    ),
    Expanded = Table.Combine(#"Grouped Rows"[AllRows])
in
    Expanded

 

Here is the result:: 

latimeria_0-1687811504816.png

 

View solution in original post

6 REPLIES 6
slorin
Super User
Super User

Hi,

I find that there is a simpler function to answer your question

Group by ID, use Table.Max and a list

Then expand the record and the list

let
Source = YourSource,
Group = Table.Group(
Source,
{"Project ID"},
{{"Max", each Table.Max(_,"Dataset Date"), type record},
{"Dataset Date", each [Dataset Date], type list}}
),
Expand_Record = Table.ExpandRecordColumn(Group, "Max", {"ID Priority", "Stack Rank"}, {"ID Priority", "Stack Rank"}),
Expand_List = Table.ExpandListColumn(Expand_Record, "Dataset Date")
in
Expand_List

 Stéphane

latimeria
Solution Specialist
Solution Specialist

Hi @slorin ,

You are right, is shorter tu use table.max.

But i prefer to use table.transform with table.combine, it is more robust in my mind as you don't need to specify fields names to expand.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MjAyUtJRcnRyNjQyBjKME4GEqaFSrA5YgQGmgiQgYWIBU2CEoQDEMTaHySPbYGJqht8GqAKYDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dataset Date" = _t, #"Project ID" = _t, #"ID priority" = _t, #"Stack Rank" = _t]),
    #"Grouped Rows" = Table.Group(Source, 
        {"Project ID"}, 
        {
            {"AllRows", 
            each 
                let
                    // get max value by group
                    MaxRecordDate = Table.Max(_,"Dataset Date"),
                    // replaced values in the Group
                    ReplacedValue = Table.TransformColumns(
                        _,
                        {
                            { "ID priority",  (r)=> MaxRecordDate[ID priority] },
                            { "Stack Rank",  (r)=> MaxRecordDate[Stack Rank] }
                        }
                    )
                in 
                    ReplacedValue
            }
        }
    ),
    Expanded = Table.Combine(#"Grouped Rows"[AllRows])
in
    Expanded

 

Hi @slorin I'll give that a shot as well. Shorter is always nicer, especially considering this is part of a multi-step process whereby I am appending multiple historic spreadsheets. With each historic spreadsheet I add, I have to first run an ETL query on the new spreadsheet, append it to the main query, sort rows, remove duplicates from the previous load, and then do this grouping and expanding before repeating the process all over again. Is it sustainable? No, but I suppose that's a problem for another day. 🙂

@slorin I just tried out your code, and while short, which is great, it also appears to produce an ID Priority column with all null values.

latimeria
Solution Specialist
Solution Specialist

Hi @Gabe_V ,

 

You can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MjAyUtJRcnRyNjQyBjKME4GEqaFSrA5YgQGmgiQgYWIBU2CEoQDEMTaHySPbYGJqht8GqAKYDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dataset = _t, #"Project ID" = _t, #"ID priority" = _t, #"Stack Rank" = _t]),
    #"Grouped Rows" = Table.Group(Source, 
        {"Project ID"}, 
        {
            {"AllRows", 
            each 
                let
                    // get max value by group
                    Priority = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[ID priority]{0},
                    Stack = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[Stack Rank]{0},
                    // replaced values in the Group
                    ReplacedValue = Table.TransformColumns(
                        _,
                        {
                            { "ID priority",  (r)=> Priority },
                            { "Stack Rank",  (r)=> Stack }
                        }
                    )
                in 
                    ReplacedValue
            }
        }
    ),
    Expanded = Table.Combine(#"Grouped Rows"[AllRows])
in
    Expanded

 

Here is the result:: 

latimeria_0-1687811504816.png

 

Thank you very much @latimeria! I only had to do a tiny tweak (column name) and it works great, and I am very happy that it groups by Project ID as well. I had actually forgot to add that piece into the request, but I see by the nature of the grouping that it accommodates that. Very helpful!

I hope this helps others down the road. 😀

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors