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
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 

Anonymous
Not applicable

try column from example

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors