Forum Discussion

Draszor's avatar
Draszor
Icon for Helper III rankHelper III
4 years ago

Replace Value based on latest date - how to apply the solution

Hi,   I want to replace project name ("Column6" of my table) for each project number ("Project" column of my table) with the name used for most fresh "Extraction Date". I found the solution it her...
Need Help
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

That code is pretty difficult for me to read.

 

Here's something similar that should be clearer to follow:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDQzMQZWqgFKuDImwJpAwtIcJGQLYzRNgcSBkbm8KFXSDCFiDVpujCRgZIwsZAthtY2AAkbGRsgCYMsdIAodoVYgjIUYZmQNWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, ProjectName = _t, Year = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"ProjectName", type text}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectID"}, {{"ProjectName", each Table.Max(_, "Year")[ProjectName], type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ProjectID"}, #"Grouped Rows", {"ProjectID"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"ProjectName"}, {"ProjectNameLatest"})
in
    #"Expanded Grouped Rows"

Let me know if you have questions.

 

Note: This ^^ is analogous to Approach #3 I wrote about here:

https://community.powerbi.com/t5/Community-Blog/Select-Distinct-Rows-Ordered-by-Another-Column-Power-Query/ba-p/2141505