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!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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
Top Kudoed Authors