Power Query | Get Max Date (2 Columns)

[ovetteabejuela]

I'd like to capture (maybe in a different column) the max date(Week) under a single month(Month). I think I can manually upadte this kind of data but I am curious though as to how to actually do it via PowerQuery(if possible).

Note: this is just for easy viewing, they are in a single column (2 columns), actual data is below it

Week	Month
1/14/2018	1/1/2018
1/14/2018	1/1/2018
1/21/2018	1/1/2018
1/21/2018	1/1/2018
1/21/2018	1/1/2018
1/21/2018	1/1/2018
1/28/2018	1/1/2018
1/28/2018	1/1/2018
1/28/2018	1/1/2018
1/28/2018	1/1/2018
2/4/2018	2/1/2018
2/4/2018	2/1/2018
2/4/2018	2/1/2018
2/4/2018	2/1/2018
2/11/2018	2/1/2018
2/11/2018	2/1/2018
2/11/2018	2/1/2018
2/11/2018	2/1/2018
2/18/2018	2/1/2018
2/18/2018	2/1/2018
2/18/2018	2/1/2018
2/25/2018	2/1/2018
2/25/2018	2/1/2018
2/25/2018	2/1/2018
3/4/2018	3/1/2018
3/4/2018	3/1/2018
3/4/2018	3/1/2018
3/11/2018	3/1/2018
3/11/2018	3/1/2018
3/11/2018	3/1/2018
3/18/2018	3/1/2018
3/18/2018	3/1/2018
3/18/2018	3/1/2018
3/25/2018	3/1/2018
3/25/2018	3/1/2018
3/25/2018	3/1/2018
4/1/2018	4/1/2018
4/1/2018	4/1/2018
4/1/2018	4/1/2018
4/8/2018	4/1/2018
4/8/2018	4/1/2018
4/8/2018	4/1/2018
4/15/2018	4/1/2018
4/15/2018	4/1/2018
4/15/2018	4/1/2018
4/22/2018	4/1/2018
4/22/2018	4/1/2018
4/22/2018	4/1/2018
4/29/2018	5/1/2018
4/29/2018	5/1/2018
4/29/2018	5/1/2018
5/6/2018	5/1/2018
5/6/2018	5/1/2018
5/6/2018	5/1/2018
5/13/2018	5/1/2018
5/13/2018	5/1/2018
5/13/2018	5/1/2018
5/20/2018	5/1/2018
5/20/2018	5/1/2018
5/20/2018	5/1/2018
5/27/2018	5/1/2018
5/27/2018	5/1/2018
5/27/2018	5/1/2018

[Anonymous · Accepted solution]

@ovetteabejuela,

One way I could think of is to duplicate your original query, perform Group By transformation by Month with MAX aggregation on Week, then merge join the orginal query with the newly grouped query on Month column

[ovetteabejuela]

I can't try that now but I see what you want me to do. I think that could be it! I'll let you know how it panned out after I try it.

[v-chuncz-msft]

@ovetteabejuela,

By the way, SUMMARIZECOLUMNS Function, SUMMARIZE Function and GROUPBY Function could be used in DAX.