Adding month to date in power query and filtering out maximum dates in a different column

[kkjoy81]

I use 'power query' through excel, not through powerbi. There are differences. I have found similar threads to what i am after, however they're not 'excel power query' specific and so don't work unfortunately. 

1) In Powerquery, i have two columns. One with whole numbers which represent months, and one with date '01/02/2009.' I want to produce a column that adds the date to the month. I know that i have to change the format of the months column, so i have changed it to text... but would love to hear recommendations around this. 

2) In another column i have 'reporting_date' and i want to filter it out so that only the maximum dates are filtered automatically. I don't want to have to go into power query and toggle the 'filters' on or off each time the data updates. 

Any advice or recommendations, thanks!  

[Vijay_A_Verma]

Below will work in both PBI Desktop and Excel PQ (I will love to hear what doesn't work in both)

1. Let's assume date column is named Date and month column is Month. Then to add months, use below formula. Date column needs to be in date format and month should be in whole number format not text format.

= Date.AddMonths([Date],[Month])

if you are keeping Month in text format, then

= Date.AddMonths([Date],Number.From([Month]))

2. To filter on maximum date, issue following statement

 = Table.SelectRows(#"Added Custom", each ([reporting_date] = List.Max(#"Added Custom"[reporting_date])))

Where #"Added Custom" should be replaced by your previous step

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3NNA3MlLSUTIFYiAPxInViVYyhTB1lAyNwBLmCAkjY4iMBVjCAiZhom9oBJEwQJUwhhsF0QHWHhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Month = _t, reporting_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddMonths([Date],[Month]), type date),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([reporting_date] = List.Max(#"Added Custom"[reporting_date])))
in
    #"Filtered Rows"