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?

[TABLE="width: 266"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Order[/TD]
[TD]Report Date[/TD]
[TD]Latest Date[/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/10/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/11/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/14/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/15/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/17/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/18/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/21/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]10/22/2019[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]12444[/TD]
[TD]10/10/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12444[/TD]
[TD]10/11/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12444[/TD]
[TD]10/14/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12444[/TD]
[TD]10/15/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12444[/TD]
[TD]10/17/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15555[/TD]
[TD]10/10/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15555[/TD]
[TD]10/11/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15555[/TD]
[TD]10/14/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/10/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/11/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/14/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/15/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/17/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/18/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/21/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32323[/TD]
[TD]10/22/2019[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Order[/td][td=bgcolor:#70AD47]Report Date[/td][td=bgcolor:#70AD47]Latest Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12123​
[/td][td=bgcolor:#E2EFDA]
10/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12123​
[/td][td]
11/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12127​
[/td][td=bgcolor:#E2EFDA]
14/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12123​
[/td][td]
15/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12123​
[/td][td=bgcolor:#E2EFDA]
17/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12123​
[/td][td]
18/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12123​
[/td][td=bgcolor:#E2EFDA]
21/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12123​
[/td][td]
22/10/2019​
[/td][td]
22/10/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12444​
[/td][td=bgcolor:#E2EFDA]
10/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12444​
[/td][td]
11/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12444​
[/td][td=bgcolor:#E2EFDA]
14/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
12444​
[/td][td]
15/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
12444​
[/td][td=bgcolor:#E2EFDA]
17/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
15555​
[/td][td]
10/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
15555​
[/td][td=bgcolor:#E2EFDA]
11/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
15555​
[/td][td]
14/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
32323​
[/td][td=bgcolor:#E2EFDA]
10/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
32323​
[/td][td]
11/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
32323​
[/td][td=bgcolor:#E2EFDA]
14/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
32323​
[/td][td]
15/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
32323​
[/td][td=bgcolor:#E2EFDA]
17/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
32323​
[/td][td]
18/10/2019​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
32323​
[/td][td=bgcolor:#E2EFDA]
21/10/2019​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
32323​
[/td][td]
22/10/2019​
[/td][td]
22/10/2019​
[/td][/tr]
[/table]


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,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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