cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Replace values by latest date Power Query

I am trying to replace the Street Address with the Street Address of 2020 (latest date). Some rows include City, State and Zip but I already have columns for those so i just want to keep one standard address. THis will help when i have to pivot later but need this first to give me one row per store.Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Replace values by latest date Power Query

Check out the M code below to do this:

It turns this

2020-04-23 12_13_18-Untitled - Power Query Editor.png

into this:

2020-04-23 12_13_31-Untitled - Power Query Editor.png

 

M code:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUosTkkDUkYGRgZKsToIMSgGSxlagqWSQBwTYyNjE0OjQmMTZF0gqZKMDKCe9HIghuuKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Address = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Address", type text}, {"Year", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"All Rows", each _, type table [Customer=text, Address=text, Year=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Latest Address", each Table.Max([All Rows],"Year")[Address]),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Custom", "All Rows", {"Address", "Year"}, {"Address", "Year"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"Customer", "Latest Address", "Year"})
in
    #"Removed Other Columns"


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
Highlighted
Super User VI
Super User VI

Re: Replace values by latest date Power Query

Check out the M code below to do this:

It turns this

2020-04-23 12_13_18-Untitled - Power Query Editor.png

into this:

2020-04-23 12_13_31-Untitled - Power Query Editor.png

 

M code:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUosTkkDUkYGRgZKsToIMSgGSxlagqWSQBwTYyNjE0OjQmMTZF0gqZKMDKCe9HIghuuKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Address = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Address", type text}, {"Year", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"All Rows", each _, type table [Customer=text, Address=text, Year=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Latest Address", each Table.Max([All Rows],"Year")[Address]),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Custom", "All Rows", {"Address", "Year"}, {"Address", "Year"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"Customer", "Latest Address", "Year"})
in
    #"Removed Other Columns"


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors