Power Query - Pull Over Latest Date From Another Column

lager1001

Board Regular
Joined
May 17, 2019
Messages
88
This would seemingly be simple to do. I have a "Report Date"column with hundreds of rows of dates. I need a custom column that indicates with an "x" or a flag of some type if each row in the Report Date column contains the latest report date within the column itself. Not today's date but the latest date found in the column. I can do this in Excel but run into trouble trying to write it in power query. Any ideas?

OrderReport DateLatest Date
1212310/10/2019
1212310/11/2019
1212310/14/2019
1212310/15/2019
1212310/17/2019
1212310/18/2019
1212310/21/2019
1212310/22/2019x
1244410/10/2019
1244410/11/2019
1244410/14/2019
1244410/15/2019
1244410/17/2019
1555510/10/2019
1555510/11/2019
1555510/14/2019
3232310/10/2019
3232310/11/2019
3232310/14/2019
3232310/15/2019
3232310/17/2019
3232310/18/2019
3232310/21/2019
3232310/22/2019x

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
you can try with Group and Max from Report Date
or
Table.Max from Report Date

but this is not an Excel worksheet way
 
Last edited:
Upvote 0
is this acceptable?

OrderReport DateLatest Date
12123​
10/10/2019​
12123​
11/10/2019​
12127​
14/10/2019​
12123​
15/10/2019​
12123​
17/10/2019​
12123​
18/10/2019​
12123​
21/10/2019​
12123​
22/10/2019​
22/10/2019​
12444​
10/10/2019​
12444​
11/10/2019​
12444​
14/10/2019​
12444​
15/10/2019​
12444​
17/10/2019​
15555​
10/10/2019​
15555​
11/10/2019​
15555​
14/10/2019​
32323​
10/10/2019​
32323​
11/10/2019​
32323​
14/10/2019​
32323​
15/10/2019​
32323​
17/10/2019​
32323​
18/10/2019​
32323​
21/10/2019​
32323​
22/10/2019​
22/10/2019​

Code:
[SIZE=1]// TableMaxDate
let
    Source = Excel.CurrentWorkbook(){[Name="[I][COLOR="#FF0000"]YourTableName[/COLOR][/I]"]}[Content],
    Tmax = Table.PromoteHeaders(Table.SelectColumns(Table.Transpose(Record.ToTable(Table.Max(Source,"Report Date"))),{"Column2"}), [PromoteAllScalars=true]),
    Merge = Table.NestedJoin(Source,{"Report Date"},Table.RenameColumns(Tmax,{{"Report Date", "Latest Date"}}),{"Latest Date"},"Latest Date",JoinKind.LeftOuter),
    Type = Table.TransformColumnTypes(Table.ExpandTableColumn(Merge, "Latest Date", {"Latest Date"}, {"Latest Date"}),{{"Report Date", type date}, {"Latest Date", type date}})
in
    Type[/SIZE]
 
Upvote 0
or even this

Code:
[SIZE=1]// TableMaxDate
let
    Source = Excel.CurrentWorkbook(){[Name="[I][COLOR="#FF0000"]YourTableName[/COLOR][/I]"]}[Content],
    TMax = Table.PromoteHeaders(Table.SelectColumns(Table.Transpose(Record.ToTable(Table.Max(Source,"Report Date"))),{"Column2"}), [PromoteAllScalars=true]),
    Result = Table.ExpandTableColumn(Table.NestedJoin(Source,{"Report Date"},TMax,{"Report Date"},"Latest Date",JoinKind.LeftOuter), "Latest Date", {"Report Date"}, {"Latest Date"}),
    Type = Table.TransformColumnTypes(Result,{{"Report Date", type date}, {"Latest Date", type date}})
in
    Type[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top