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

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

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