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
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 |
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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 |
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
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:
Pete
Proud to be a Datanaut!
Hi Thank you very much
try column from example
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
66 | |
62 | |
21 | |
18 | |
12 |