Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 columns in my dataset - these columns are used to show the financial month and year period. Because of this they are not formatted in a friendly way for use in formulas so I have a need to transform the data.
Column 1 (Month) is in the format "<month_number>-<month_name>". This is based on our financial year, so May is month 1. So values show as, "01-May", "02-June", through to "12-April".
Column 2 (Year) shows the year, but in the format "18/19", "19/20", as the financial year straddles 2 years.
I need to standardise the data in these fields in so I can compare with other date fields that are in standard date format.
I know I can use a delimiter to split the Month column so it can simply show as Jan, Feb, Mar etc., but the battle is assigning the correct year to each month.
Based on year 18/19, the logic basically is that if the month is 8 (December) or lower, the year needs to return the lower value (i.e. 18). If the year is 9 (January) or higher, it needs to revert to the higher value (i.e. 19).
Any assistance would be greatly appreciated.
Solved! Go to Solution.
Hi Danbert,
I am confused. The formula below (which is from the code above) does what you are asking and does not require editing.
Regards,
Mike
Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)
Hi Danbert,
This problem is easier then it appears on first glance. The approach is to 1) create a date where the year is the first two digits of the Year and the first two digits of the month. This now converts May to Jan-2018, June to Feb-2018, ..., March to Nov-2018 and April to Dec-2018. Now 2) add four months to the date to get May-2018,Jun-2018, .., Mar-2019 and Apr-2019. The method is implemented as a custom column below.
Regards,
Mike
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], AddYear = Table.AddColumn(Source, "Date", each Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)) in AddYear
=if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "18/19" then 2018 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "18/19" then 2019 else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "19/20" then 2019 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "19/20" then 2020 else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "20/21" then 2020 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "20/21" then 2021 else if ([Month] = "01-May" or [Month] = "02-June" or [Month] = "03-July" or [Month] = "04-August" or [Month] = "05-September" or [Month] = "06-October" or [Month] = "07-November" or [Month] = "08-December") and [Year] = "21/22" then 2021 else if ([Month] = "09-January" or [Month] = "10-February" or [Month] = "11-March" or [Month] = "12-April") and [Year] = "21/22" then 2022 else 0
I created a further custom field (converting the Months to Numbers), merged the new field and the Year field together and converted to date format. They showed up in the format 01/02/2019, 01/03/2019 etc, but perfect for my needs.
Hi Danbert,
I am confused. The formula below (which is from the code above) does what you are asking and does not require editing.
Regards,
Mike
Date.AddMonths(#date(2000+Number.From(Text.Start([Year],2)),Number.From(Text.Start([Month],2)),1),4)
Mike, that worked perfectly, thank you so much.
Dan
Hi Mike
Apologies, I'm still new to this and managed my own rudimentary solution to get me past a block. Your initial post referenced an excel worksheet and I'm not clear on the syntax to amend that to match my situation in a query in Power BI. I will try your amended suggestion also when I get a chance and will post back and mark as a solution if it works for me.
Thanks again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.