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

[shri0025]

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 .

Business	Year	Month	Quarter	Division	Qty	State	Branch
A	2018-19	Apr	Q1	2B	1	Andhra Pradesh	South
A	2018-19	Apr	Q1	2B	3	Bihar	East
A	2018-19	Apr	Q1	2B	5	Chandigarh	North 1
A	2018-19	Aug	Q2	2B	11	Kerala	South
A	2019-20	Apr	Q1	2B	111	Bihar	East
A	2019-20	Apr	Q1	2B	111	Chandigarh	North 1
A	2019-20	Jan	Q4	2B	123	Andhra Pradesh	South
A	2019-20	Jan	Q4	2B	123	Bihar	East
A	2020-21	Jan	Q4	2B	124	Andhra Pradesh	South
A	2020-21	Jun	Q1	2B	234	Delhi	North 1
A	2021-22	Jul	Q2	2B	321	Chhattisgarh	Central

[BA_Pete · Accepted solution]

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:

Pete

[BA_Pete · Accepted solution]

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:

Pete

[shri0025]

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

Business	Month	Division	Qty	Branch
A	Apr	2B	1	South
A	May	2B	3	East
A	Jun	2B	5	North 1
A	Jul	2B	11	South
A	Aug	2B	111	East
A	Sep	2B	111	North 1
A	Oct	2B	123	South
A	Nov	2B	123	East
A	Dec	2B	124	South
A	Jan	2B	234	North 1
A	Feb	2B	321	Central
A	Apr	2B	5	South
A	May	2B	15	East
A	Jun	2B	25	North 1
A	Jul	2B	55	South
A	Aug	2B	555	East
A	Sep	2B	555	North 1
A	Oct	2B	615	South
A	Nov	2B	615	East
A	Dec	2B	620	South
A	Jan	2B	1170	North 1
A	Feb	2B	1605	Central

[BA_Pete]

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:

Pete

[shri0025]

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

[BA_Pete · Accepted solution]

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:

Pete

[shri0025]

Hi Thank you very much 

[Anonymous]

try column from example