cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shri0025
Helper II
Helper II

How To create Date Column From Month and Year column 2018-19 in Power Query

Hi  I am New in Power BI  Please help to me How To create Date Column From Month and Year column 2018-19 in Power Query .

BusinessYearMonthQuarterDivisionQtyStateBranch
A2018-19AprQ12B1Andhra PradeshSouth
A2018-19AprQ12B3BiharEast
A2018-19AprQ12B5ChandigarhNorth 1
A2018-19AugQ22B11KeralaSouth
A2019-20AprQ12B111BiharEast
A2019-20AprQ12B111ChandigarhNorth 1
A2019-20JanQ42B123Andhra PradeshSouth
A2019-20JanQ42B123BiharEast
A2020-21JanQ42B124Andhra PradeshSouth
A2020-21JunQ12B234DelhiNorth 1
A2021-22JulQ22B321ChhattisgarhCentral
2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @shri0025 ,

 

Try adding a custom column in Power Query and using this calculation:

Date.From(
    Text.Combine(
        {
        "01",
        [Month],
        if List.Contains({"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, [Month])
        then Text.Start([Year], 4)
        else Text.Combine({"20", Text.End([Year], 2)})
        }
    )
)

 

I'm assuming that your [Year] field shows financial year, and that your financial year start is 1st April.

 

This gives me the following output:

BA_Pete_0-1632825169116.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

Hi @shri0025 ,

 

Try this new column:

Date.From(
    Text.Combine(
        {
        "01",
        [Month],
        if
		(not List.Contains({"Jan", "Feb", "Mar"}, [Month])
			and Date.Month(DateTime.LocalNow()) >= 4)
		or
		(List.Contains({"Jan", "Feb", "Mar"}, [Month])
			and Date.Month(DateTime.LocalNow()) < 4)		
        then
		Text.From(Date.Year(DateTime.LocalNow()))
		else if
		not List.Contains({"Jan", "Feb", "Mar"}, [Month])
			and Date.Month(DateTime.LocalNow()) < 4
		then
		Text.From(Date.Year(DateTime.LocalNow()) - 1)
        else
		Text.From(Date.Year(DateTime.LocalNow()) + 1)
        }, "-"
    )
)

 

I've tried to make this as dynamic as possible, so the date years will increase by 1 on 1st April each year, on the assumption that this table only ever holds current FY data.

 

I get the following output:

BA_Pete_1-1632919392109.png

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
BA_Pete
Super User
Super User

Hi @shri0025 ,

 

Try adding a custom column in Power Query and using this calculation:

Date.From(
    Text.Combine(
        {
        "01",
        [Month],
        if List.Contains({"Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, [Month])
        then Text.Start([Year], 4)
        else Text.Combine({"20", Text.End([Year], 2)})
        }
    )
)

 

I'm assuming that your [Year] field shows financial year, and that your financial year start is 1st April.

 

This gives me the following output:

BA_Pete_0-1632825169116.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Thanks one More issue face  My one table only month  please help  how to create date column

BusinessMonthDivisionQtyBranch
AApr2B1South
AMay2B3East
AJun2B5North 1
AJul2B11South
AAug2B111East
ASep2B111North 1
AOct2B123South
ANov2B123East
ADec2B124South
AJan2B234North 1
AFeb2B321Central
AApr2B5South
AMay2B15East
AJun2B25North 1
AJul2B55South
AAug2B555East
ASep2B555North 1
AOct2B615South
ANov2B615East
ADec2B620South
AJan2B1170North 1
AFeb2B1605Central

Hi @shri0025 ,

 

In order to be a valid date field, you must have a year.

You can use this in a new custom column to convert your months to the first of the month in current year:

Text.Combine({"01", [Month]}, "-")

 

It will create a text field of day-month, but when you change the data type to Date you'll get the following output:

BA_Pete_0-1632900297006.png

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi  Great work  some  below data FY 2021-22   Jan month show  Jan 21 but i need jan 22 show please help 

Hi @shri0025 ,

 

Try this new column:

Date.From(
    Text.Combine(
        {
        "01",
        [Month],
        if
		(not List.Contains({"Jan", "Feb", "Mar"}, [Month])
			and Date.Month(DateTime.LocalNow()) >= 4)
		or
		(List.Contains({"Jan", "Feb", "Mar"}, [Month])
			and Date.Month(DateTime.LocalNow()) < 4)		
        then
		Text.From(Date.Year(DateTime.LocalNow()))
		else if
		not List.Contains({"Jan", "Feb", "Mar"}, [Month])
			and Date.Month(DateTime.LocalNow()) < 4
		then
		Text.From(Date.Year(DateTime.LocalNow()) - 1)
        else
		Text.From(Date.Year(DateTime.LocalNow()) + 1)
        }, "-"
    )
)

 

I've tried to make this as dynamic as possible, so the date years will increase by 1 on 1st April each year, on the assumption that this table only ever holds current FY data.

 

I get the following output:

BA_Pete_1-1632919392109.png

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Thank you very much 

yakm
Helper I
Helper I

try column from example

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.