I've spent the entire day trying to figure out how to use power query to get a day of week in my date table and cant figure it out.
It shouldnt be text as when placed on a graph it should sort as mon, tue,wed, thu, fri sat, sun. Similarly, looking for this to be within months. i.e Jan,Feb Mar....
I tried #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName.monday([Date]), type text), but it displays a number as 0,1,2,3,4,5,6...I could settle for this however would prefer a day 7 rather than zero.
Mon needs to be 1st day of the week. thanks in advance
Best Answer
To Show day as three letters and sort the day by day number considering Monday to be 1 you can create a new table under data view using the below query then sort the "Day" on the "Day Num" from data view
_Calendar = ADDCOLUMNS(CALENDAR("01-Jan-2023","31-Dec-2025"),"Month Year",FORMAT([Date],"MMM YYYY"), "Year",YEAR([Date]),"WeekNum",WEEKNUM([Date],2),"Day", FORMAT([Date],"DDD"),"Day Num",WEEKDAY([Date],2))Note: Date provided on the above code is just an example
In powerquery, if the date is in Column1, add column, custom column with formula
To get Monday, Tuesday, Wednesday
= Date.DayOfWeekName([Column1])To get Mon, Tue, Wed
= Text.Start(Date.DayOfWeekName([Column1]),3) To get a number, where Mon=1 and Sunday=7
= 1+Date.DayOfWeek([Column1],Day.Monday)