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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors