Power Query - Pull Over Latest Date From Another Column

L

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

S
  • Solved
Consider data based on latest date in Power BI editor
Replies
2
Views
124
shansakhi
S
G
  • Question Question
2 Table Running Total Not Working in Power BI
Replies
0
Views
482
GeeWhiz7
G
U
  • Question Question
Power Query chose a value <=date
Replies
1
Views
208
AlienSx
A
L
  • Solved
Power Query to Determine Consecutive and Non-Consecutive Dates
2 3
Replies
26
Views
1K
legalhustler
L
L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

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