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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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