how to transform data in year and month

[lilskyy4202]

Hi all

I have a data sheet generate from system in fiscal year and the header are without the year.

For example:

April to Dec = 2021

Jan to Mar = 2022

 i tried the following formula, but it does not seem to work.

if Text.Middle([Attribute], 3, 3) = "Jan" or Text.Middle([Attribute], 3, 3) = "Feb" or Text.Middle([Attribute], 3, 3) = "Mar"
then [Attribute] & "-2022"
else [Attribute] & "-2021"

Is there any way to transform this data. I am new to this powerbi and hoping to learn something new here 🙂

Thanks for your help

[Bibiano_Geraldo]

Hi @lilskyy4202 ,

First to work with your data in power bi, you need to Unpivot the columns in power query:

1- Select the column with A,B,C rows and then choose unpivot other columns:

Now your data should look like this:

2- Add custom column and paste the following M code:

let
    CurrentYear = 2022
in
    if Date.Month(Date.FromText("01 " & [Month] & " 2000")) >= 4 
    then #date(CurrentYear-1, Date.Month(Date.FromText("01 " & [Month] & " 2000")), 1)
    else #date(CurrentYear, Date.Month(Date.FromText("01 " & [Month] & " 2000")), 1)

4- Change the column data type for Date:

[danextian · Accepted solution]

Hi @lilskyy4202 

Since you're using the attribute column, I assume you have already unpivoted your data. Try the following

let 
// converting to mmm-yy format so Power Query can parse it as a date with Date.From
mmmyy = Date.From([Attribute] & "-24"),
//get the month number
MonthNumber = Date.Month(mmmyy),
//get the year 
yr = if MonthNumber >=4 then 2021 else 2022
in [Attribute] & "-" & Text.From(yr)

[lilskyy4202]

Thank you so much, It works!