Forum Discussion
Friendly Week Names
Hello,
In Excel it is real easy to create friendly week names such as:
| From Jul 9 to Jul 15 |
| From Jul 16 to Jul 22 |
| From Jul 30 to Aug 5 |
| From Aug 6 to Aug 12 |
| From Aug 13 to Aug 19 |
| From Aug 20 to Aug 26 |
| From Aug 27 to Sep 2 |
| From Sep 3 to Sep 9 |
Is there any way to do this in Power BI from a Date field?
Hi lcasey
In Queries Editor,
Add custom columns
weeknum=Date.WeekOfYear([date], Day.Monday)
start of week=Date.StartOfWeek([date], Day.Monday)
end of week=Date.EndOfWeek([date], Day.Monday)Click on column [date] column, select "Add column"->Date, insert "Name of Month"
Click on column [start of week] column, select "Add column"->Date, insert "Day"
Click on column [end of week] column, select "Add column"->Date, insert "Day"
Click on column [Month Name] and [Day], select "Add column"->"Merge columns" (separator:space)
Click on column [Month Name] and [Day.1], select "Add column"->"Merge columns" (separator:space)
Add a custom column
Custom=Text.Combine({"From", [Merged],"to",[Merged.1]}, " ")Then ypu could remove other columns, only keep the last column
Best Regards
Maggie
10 Replies
Hi lcasey
In Queries Editor,
Add custom columns
weeknum=Date.WeekOfYear([date], Day.Monday)
start of week=Date.StartOfWeek([date], Day.Monday)
end of week=Date.EndOfWeek([date], Day.Monday)Click on column [date] column, select "Add column"->Date, insert "Name of Month"
Click on column [start of week] column, select "Add column"->Date, insert "Day"
Click on column [end of week] column, select "Add column"->Date, insert "Day"
Click on column [Month Name] and [Day], select "Add column"->"Merge columns" (separator:space)
Click on column [Month Name] and [Day.1], select "Add column"->"Merge columns" (separator:space)
Add a custom column
Custom=Text.Combine({"From", [Merged],"to",[Merged.1]}, " ")Then ypu could remove other columns, only keep the last column
Best Regards
Maggie
Thank you!
This is exactly what I was trying to do!
There is no option to insert column.
Where do you add colum? I selected date but there is no insert option.
Ok got it---- Here is a picture in case anyone needs :
There is an issue with this.
Look at how the dates for October show:
It's done pretty much the same way, usually with a Date table and WEEKNUM function
True, I do understanbd that, but here is what I get when using weekno function:
1
2
3
4
5
6
7
8
What I need is
Jan 1 - Jan 7
Jan 8 - Jan 14
Jan 15 - Jan 21
Jan 22 - Jan 28
Rather than the numbers.
