Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkjoy81
Regular Visitor

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

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!  

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors