There are hundreds of different methods for generating a Date dimension table for use in Power Pivot: you can use TSQL, Excel formulas, import from the DateStream table in the Windows Azure Marketplace, and there’s even an app for it. Nevertheless I thought it would be a good idea to solve this problem in Power Query because:
- It seems like the natural tool to use, and a date table is one of the first tables a data steward will want to share with his/her users.
- It’s a very good way to learn about and demonstrate all the date functionality in M and Power Query. The more M code examples that there are out there, the better, right?
- Having just signed the contract to write a whole book on Power Query I need to master M very quickly!
Here’s how I did it. First of all, I decided to implement a function to create the table rather than a regular query; this function takes two parameters: the start date and the end date of the range of dates to appear in the table. Here’s the function I came up with:
let
CreateDateTable = (StartDate, EndDate) =>
let
/*StartDate=#date(2012,1,1),
EndDate=#date(2013,12,31),*/
//Create lists of month and day names for use later on
MonthList = {"January", "February", "March", "April", "May", "June"
, "July", "August", "September", "October", "November", "December"},
DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},
//Find the number of days between the end date and the start date
NumberOfDates = Duration.Days(EndDate-StartDate)+1,
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}
, null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
//Add custom columns for day of month, month number, year
DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),
//Since Power Query doesn't have functions to return day or month names,
//use the lists created earlier for this
MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),
//Add a column that returns true if the date on rows is the current date
IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))
in
IsToday
in
CreateDateTable
Some things to point out:
- Look – comments! Both multi-line /* */ and single line //. Adding comments to complex Power Query queries and functions is of course a Good Thing and something we should all be doing.
- The way I created the table from the start and end date is quite interesting: the List.Dates() function allows you to generate a list of dates from one date to another; I was then able to take this list and turn it into a table with Table.FromList().
- Adding extra columns to the table for the year and month number and so on is fairly straightforward. However, as far as I can see, Power Query does not have the ability to return month or day names (as opposed to numbers) from a date, so to get round this I created two lists called MonthList and DayList containing the months of the year and the days of the week, in order, took the day and month numbers and used these to find the name at the nth position in the list (see the MonthName and DayName steps).
- Date.IsInCurrentDay() compares a date with the current system date and allows you to see if a date is today’s date. There are a whole bunch of other interesting functions like this such as Date.IsInPreviousWeek(), Date.IsInCurrentYear() and so on, useful for creating relative date dimension tables like the one I describe here.
With the function created it’s very easy to generate a table using it. For example, I created a parameter table with start date and end date columns like so:
I then used this table as the starting point for a new query that called my function:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}
, {"End Date", type date}}),
CallCreateDateTable = CreateDateTable(ChangedType[Start Date]{0}
,ChangedType[End Date]{0})
in
CallCreateDateTable
Here’s the output:
You can download my demo workbook with all the code in from here.
PS If you haven’t downloaded the November update for Power Query already, I recommend you do so – the UI has been improved a lot.
Did you intend to leave a link in your last bullet?
http://cwebbbi.wordpress.com/2013/01/24/building-relative-date-reports-in-powerpivot/
Also, Chris, do you have any resources about the full list of file properties/attributes that can be queried via PowerQuery?
In particular, I am currently running a query that returns a list of files in a folder (directory) and a number of properties related to those files, such as Date Created, Date Modified, File Size, etc.
I would like to retrieve the “Last Modified By” or “Owner” property of those files. Any help would be much appreciated.
Yes, I did forget that link, thanks!
Sorry, I don’t have a list of file properties… I only have the Language and Library Specification documents.
Hi Chris
Great news that you are writing a Powerquery book – I can’t wait.
I have also looked at creating a date dimension that looks similar to yours. I have also combined it with an external calendar website called http://www.timeanddate.com/holidays/uk/ to automatically append UK holidays to it. Here is my code – inevitably not as well written as yours
let paramquery = (mystartdate as date, myenddate as date) =>
let
mydays = Number.From(myenddate) – Number.From(mystartdate) + 1 ,
myDates = List.Dates(mystartdate, mydays, Duration.From(1)),
TableFromList = Table.FromList(myDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ChangedType1 = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType1,{{“Column1”, “FullDate”}}),
DayNumber = Table.AddColumn( RenamedColumns ,” DayNumber “,each Date.Day([FullDate])),
DayName = Table.AddColumn(DayNumber, “#(tab)DayName#(tab)”, each Date.ToText([FullDate],” dddd “)),
DayAbbrev = Table.AddColumn( DayName ,” DayAbbrev “,each Date.ToText([FullDate],” ddd “)),
MonthNumber = Table.AddColumn( DayAbbrev ,” MonthNumber “,each Date.Month([FullDate])),
MonthName = Table.AddColumn( MonthNumber ,” MonthName “,each Date.ToText([FullDate],” MMMM “)),
MonthAbbrev = Table.AddColumn( MonthName ,” MonthAbbrev “,each Date.ToText([FullDate],” MMM “)),
YearNumber = Table.AddColumn( MonthAbbrev ,” YearNumber “,each Date.Year([FullDate])),
DateKey = Table.AddColumn( YearNumber ,” DateKey “,each Date.ToText([FullDate],” ddmmyyyy “)),
DayofWeek = Table.AddColumn( DateKey ,” DayOfweek “,each Date.DayOfWeek([FullDate],1)+1),
DayofYear = Table.AddColumn( DayofWeek ,” DayofYear “,each Date.DayOfYear([FullDate])),
DaysInMonth = Table.AddColumn( DayofYear ,” DaysInMonth “,each Date.DaysInMonth([FullDate])),
WeekOfMonth = Table.AddColumn( DaysInMonth ,” WeekOfMonth “,each Date.WeekOfMonth([FullDate])),
WeekOfYear = Table.AddColumn( WeekOfMonth ,” WeekOfYear “,each Date.WeekOfYear([FullDate])),
ReorderedColumns = Table.ReorderColumns(WeekOfYear,{“FullDate”, “#(tab)DateKey#(tab)”, “#(tab)DayNumber#(tab)”, “#(tab)DayName#(tab)”, “#(tab)DayAbbrev#(tab)”, “#(tab)MonthNumber#(tab)”, “#(tab)MonthName#(tab)”, “#(tab)MonthAbbrev#(tab)”, “#(tab)YearNumber#(tab)”, “#(tab)DayOfweek#(tab)”, “#(tab)DayofYear#(tab)”, “#(tab)DaysInMonth#(tab)”, “#(tab)WeekOfMonth#(tab)”, “#(tab)WeekOfYear#(tab)”}),
GroupedRows = Table.Group(ReorderedColumns, {“#(tab)YearNumber#(tab)”}, {{“Count”, each Table.RowCount(_), type number}}),
ChangedType2 = Table.TransformColumnTypes(GroupedRows,{{“#(tab)YearNumber#(tab)”, type text}}),
InsertedCustom = Table.AddColumn(ChangedType2, “Custom”, each DateHolidays([#”#(tab)YearNumber#(tab)”])),
RenamedColumns2 = Table.RenameColumns(InsertedCustom ,{{“#(tab)YearNumber#(tab)”, “YearNumber2″}}),
#”Expand Custom” = Table.ExpandTableColumn(RenamedColumns2, “Custom”, {“Holiday name”, “Holiday type”, “Where it is observed”, “Custom”}, {“Custom.Holiday name”, “Custom.Holiday type”, “Custom.Where it is observed”, “Custom.Custom”}),
EndTable = Table.Join(ReorderedColumns, “FullDate”, #”Expand Custom”, “Custom.Custom”, JoinKind.LeftOuter),
SortedRows = Table.Sort(EndTable,{{“FullDate”, Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{“YearNumber2”, “Count”, “Custom.Custom”}),
RenamedColumns1 = Table.RenameColumns(RemovedColumns,{{“Custom.Holiday name”, “Holiday name”}, {“Custom.Holiday type”, “Holiday type”}, {“Custom.Where it is observed”, “Where it is observed”}})
in
RenamedColumns1
in
paramquery
Chris
Me again
You do not have to hard code names of month and days in list because you can use this
MonthName= Table.AddColumn(MonthNumber,”MonthName”,each Date.ToText([FullDate],”MMMM “)),
or this
DayName= Table.AddColumn(DayNumber, “#(tab)DayName#(tab)”, each Date.ToText([FullDate],”dddd”)),
DayAbbrev= Table.AddColumn(DayName,”DayAbbrev “,each Date.ToText([FullDate],”ddd “)),
Aha, thanks! I didn’t realise that when I was looking through the docs. Good to know!
Chris, Slightly off topic, but where can I ask to find syntax for calling SQL server based stored procedure with one text parameter and one integer parameter in Power Query M code?
Have you tried using Sql.Database? http://office.microsoft.com/en-gb/excel-help/sql-database-HA104112300.aspx?CTT=5&origin=HA104122363 I don’t know if this works, but it’s worth investigating
Thanks. The basic graphical interface brings up the Server and Table as
let
Source = Sql.Databases(“servername”),
speedus = Source{[Name=”databasename”]}[Data],
Tablename = datbasename{[Name=”Tablename”]}[Data]
in
Tablename
Or if I select an fxfunction in the database
GetComponentFromKey = databsename{[Name=”GetComponentFromKey”]}[Data]
in
GetComponentFromKey
Although I get a message “function (@key as nullable text as nullable number” with an Invoke button that I can press an enter an initial parameter.
But it doesn’t see the stored procedures in the database for me to select.
I guess I could just manually enter the Proc name in place of the function and see what goes.
Are you using the November update? That has an interface for adding your own SQL query, and you may also be able to call a stored procedure from there.
Yes, only downloaded it last week.
Yes, I’ve enabled the bit allow me to edit these, and can happily type anything, but the question remains how to actually call the procedure.
In ‘Normal’ standard Excel the query syntax is {call spFetchComponentsReport (?,?,?)} but I’ve failed to understand how I’d get Power Query ‘s M-code to mimic it.
This is probably a question to ask on the Power Query forum: http://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery
ah, http://social.technet.microsoft.com/Forums/en-US/f34ca56f-4865-4b0e-87d5-483170f0734e/use-of-stored-procedure?forum=powerquery
here (In sept) it says for a future version.
Thanks
Just been working with a client on calling stored procedures and passing required parameters using Powerquery very easy very powerful. Syntax is same as you would use in management studio ie
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;
you can change value of BusinessEntityID using PQ function capability
Typically far simpler than I expected, and why is it like TSQL which is for me would have been the best solution, so least likely for me to guess at!
‘fraid I got frustrated and used the less friendly world of VBA so solve it this time, but thanks very much for the info kind sir.
Dear Chris,
Thank you for your usefull post. Where have you defined the “CreateDateTable” function in the excel workbook? I can’t find the function defininition anywhere.
Thank you!
Simona
You need to have the latest version of Power Query installed – then go to the Power Query tab, click on the Workbook button in the Manage Queries section, and in the workbook queries pane you’ll see a query (in fact a function) called CreateDateTable.
Thank you very much Chiris. It was my fault. I haven’t the the latest version of PQ installed.
Simona
Hi Chris,
Thanks for posting this, I’ve created my own dimDate table based on this, but I can’t figure out how to get IsInCurrentWeek & IsInPreviousWeek Logical TRUE/FALSE values based off of week that starts on Monday and ends in Sunday. I’ve created my own logic for it but it breaks down at the beginning and end of the year. What else can I do?
let
// Choose Start & End Date
StartDate=#date(2013,1,1),
EndDate=#date(2015,12,31),
// Calculate number of dates
NumberOfDates = Duration.Days(EndDate-StartDate),
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}
, null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Date”, type date}}),
//Add custom columns for day of month, month number, year, etc etc
Day = Table.AddColumn(ChangedType, “Day”, each Date.Day([Date])),
DayDoubleDigit = Table.AddColumn(Day, “DayDoubleDigit”, each Date.ToText([Date],”dd”)),
MonthNumber = Table.AddColumn(DayDoubleDigit, “MonthNumber”, each Date.Month([Date])),
MonthName = Table.AddColumn(MonthNumber, “MonthName”, each Date.ToText([Date],”MMMM”)),
MonthNameShort = Table.AddColumn(MonthName, “MonthNameShort”, each Date.ToText([Date],”MMM”)),
Year = Table.AddColumn(MonthNameShort, “Year”, each Date.Year([Date])),
DayOfWeekNumber = Table.AddColumn(Year, “DayOfWeek”, each Date.DayOfWeek([Date])+1),
DayName = Table.AddColumn(DayOfWeekNumber, “DayName”, each Date.ToText([Date],”dddd”)),
DayNameShort = Table.AddColumn(DayName, “DayNameShort”, each Date.ToText([Date],”ddd”)),
IsToday = Table.AddColumn(DayNameShort, “IsToday”, each Date.IsInCurrentDay([Date])),
IsInCurrentWeek = Table.AddColumn(IsToday,”IsInCurrentWeek”,each Date.IsInCurrentWeek(Date.AddDays([Date],-1))),
IsInCurrentMonth= Table.AddColumn(IsInCurrentWeek, “IsInCurrentMonth”, each Date.IsInCurrentMonth([Date])),
IsInCurrentYear= Table.AddColumn(IsInCurrentMonth, “IsInCurrentYear”, each Date.IsInCurrentYear([Date])),
IsInPreviousWeek = Table.AddColumn(IsInCurrentYear,”IsInPreviousWeek”,each Date.IsInPreviousWeek(Date.AddDays([Date],-1))),
IsInPreviousMonth= Table.AddColumn(IsInPreviousWeek, “IsInPreviousMonth”, each Date.IsInPreviousMonth([Date])),
IsInPreviousYear= Table.AddColumn(IsInPreviousMonth, “IsInPreviousYear”, each Date.IsInPreviousYear([Date])),
SemiMonthDaySet = Table.AddColumn(IsInPreviousYear, “SemiMonthDaySet”, each (if [Day]<16 then "1-15" else Text.Combine({Text.From(16),Text.From(Date.Day(Date.EndOfMonth([Date])))},"-"))),
SemiMonthCurrentDaySet = Table.AddColumn(SemiMonthDaySet, "SemiMonthCurrentDaySet", each if Date.Day(DateTime.LocalNow()) < 16
then (if Date.IsInCurrentMonth([Date]) = Logical.FromText("True")
then (if Date.Day([Date]) 15
then “TRUE”
else “FALSE”)
else “FALSE”)),
SemiMonthPreviousDaySet = Table.AddColumn(SemiMonthCurrentDaySet, “SemiMonthPreviousDaySet”, each if Date.Day(DateTime.LocalNow()) 15
then “TRUE”
else “FALSE”)
else “FALSE”)
else (if Date.IsInCurrentMonth([Date]) = Logical.FromText(“True”)
then (if Date.Day([Date]) < 16
then "TRUE"
else "FALSE")
else "FALSE")),
QuarterOfYear= Table.AddColumn(SemiMonthPreviousDaySet, "QuarterOfYear",each Text.Combine({"Q", Text.From(Date.QuarterOfYear([Date]))}," ")),
WeekOfYear = Table.AddColumn(QuarterOfYear,"WeekOfYear",each Date.WeekOfYear(Date.AddDays([Date],-1))),
WeekNameOfYear =Table.AddColumn(WeekOfYear ,"WeekNameOfYear ",each Text.Combine({"Week",Text.PadStart(Text.From([WeekOfYear]),2,"0")}," "))
in
WeekNameOfYear
Can you give me a few more details about what’s going wrong and what you’d like to happen please?
I want the IsInCurrentWeek to start the week on Monday (like DAX function WEEKNUM(, 2)). Right now I’m calculating it like this in powerquery, Date.IsInCurrentWeek(Date.AddDays([Date],-1))), but the first day of the every year it shows like it’s last week of last year (week 53). Does the IsInCurrentWeek() have any return_type parameter like the DAX fucntion (So something like IsIncurrentWeek(,2)), I tried searching but couldn’t find any.
Could you hack it by putting in a test that says if the current week number you’re calculating is 53 and the date is January 1st, then the actual week number is 1 (otherwise return the original week number you are calculating)?
Thanks Chris, I did it the following way,
each if [Date] = Date.StartOfYear([Date]) then Date.IsInCurrentWeek([Date]) else Date.IsInCurrentWeek(Date.AddDays([Date],-1))),
Just posted a technique that produces a dynamic effect for the PQ model presented here. Thanks for all of the great inspiration you give.
http://dhexcel1.wordpress.com/2014/04/03/dynamic-write-back-to-sync-powerpivot-tables-with-date-tables-created-by-power-query/
Hi Chris,
I’ve posted this issue in powerquey forum already (link below) but I just wanted to ask if you’ve come across this. i’m using this DimDate table in many of the reports but after a recent update I’ve been getting out of line object error in many of them. Do you have any ideas what could be causing it. The screenshot and code is on the link below too.
http://social.technet.microsoft.com/Forums/en-US/41ff8e33-50fa-4196-a7f8-8f81416c28e2/trouble-refreshing-powerquery-built-datetime-tables-after-recent-update?forum=powerquery
That looks like an installation problem. Do other Power Query queries work on the same machine?
Others seem to work fine… the query works fine by itself.. but when it’s refreshed along with all other connections (powerpivot connections to an ODBC database) in a workbook.. it fails to refresh and gives the error..
This sounds like a bug to me – I recommend you report it to Microsoft.
Hi Chris,
Just one minor adjustment to the query, I think the
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0))
should be
DateList = List.Dates(StartDate, NumberOfDates+1, #duration(1, 0, 0, 0))
otherwise, it misses the last day of the year.
Thanks!
I have a table on an excel worksheet which includes a date column and has the cell values in 2 different visually represented formats; however, all the values in the date column in excel are set to “general”.
No cell value is formatted as “date” nor “number” in excel within the date column.
In the table, the dates are either displayed as 41425 or 31/05/2013. Both represent the same exact date.
When I import into Power Query and select the date column, it is automatically formatted as Data Type: Text.
If I select the entire column and select “Date” in Data Type drop down, the entire column errors out.
If I select the entire column, right click and go to Change Type > using locale, the entire column errors out.
How can I get the cell values in the Date column formatted to MM/DD/YYYY format in Power Query without altering the format/values in the data table which holds the raw data from our private server?
Thank you in advance!
I think you need to split the table into two inside Power Query, each table containing one of the two formats, then in each convert the dates and then finally combine the two tables back into one.
The blog is very useful. I am still learning MS Power BI and T-SQL. I encourage to bring more technical articles and tips for new learners.
Thank you so much, it’s very helpful for me.
One of the annoyances of date tables is that month and weekday names sort alphabetically (April, August, …) instead of calendar order (January, February, …). A great technique is to use zero-width space characters (Unicode 8203) at the beginning of these names. They don’t take up space on the screen, and you don’t have to set the sort columns after loading the table –they just sort like you expect! Here is an example query using this technique:
// Date Dimension
let
today = Date.From(DateTime.LocalNow()),
// Uncomment your choice for start date
StartDate = #date(2022, 1, 1), // Hard-coded date. Must be January 1.
//StartDate = Date.StartOfYear(List.Min(#”Query name”[Start Date])), // Table based date – change #”Query name”[Start Date] to match query and column
//StartDate = Date.AddYears(Date.StartOfYear(today), 0), // Based on today’s date. Change 0 to a number to shift years, eg. -1 for Jan 1 last year.
// Uncomment your choice for end date
EndDate = #date(2023,12,31), // Hard-coded date. Must be December 31.
//EndDate = Date.EndOfYear(List.Max(#”Query name”[End Date])), // Table based date – change #”Query name”[End Date] to match query and column
//EndDate = Date.AddYears(Date.EndOfYear(today), 0), // Based on today’s date. Change 0 to a number to shift years, eg. 1 for Dec 31 next year.
// Build a list of dates from StartDate to EndDate
Source = List.Dates(StartDate, Duration.Days(EndDate – StartDate), #duration(1, 0, 0, 0)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), type table [Date = date], null, ExtraValues.Error),
#”Added DateInt” = Table.AddColumn(#”Converted to Table”, “DateInt”, each Number.FromText( Date.ToText( [Date], [Format=”yyyyMMdd”] ) ), Int64.Type),
#”Added Year” = Table.AddColumn(#”Added DateInt”, “Year”, each Date.ToText( [Date], [Format=”yyyy”] ), type text),
#”Added Quarter” = Table.AddColumn(#”Added Year”, “Quarter”, each “Q” & Number.ToText( Date.QuarterOfYear([Date]) ), type text),
#”Added YearQuarter” = Table.AddColumn(#”Added Quarter”, “YearQuarter”, each [Year] & “-” & [Quarter], type text),
// A zero width space is repeated at front of name so that name will sort without having to set SortByColumn
#”Added Month” = Table.AddColumn(#”Added YearQuarter”, “Month”, each Text.Repeat(Character.FromNumber(8203), 12 – Date.Month([Date]))
& Date.ToText( [Date], [Format=”MMMM”] ), type text),
// A zero width space is repeated at front of name so that name will sort without having to set SortByColumn
#”Added MonthShort” = Table.AddColumn(#”Added Month”, “MonthShort”, each Text.Repeat(Character.FromNumber(8203), 12 – Date.Month([Date]))
& Date.ToText( [Date], [Format=”MMM”] ), type text),
#”Added MonthNo” = Table.AddColumn(#”Added MonthShort”, “MonthNo”, each Date.ToText( [Date], [Format=”MM”] ), type text),
// A zero width space (#8203) is repeated at front of name so that name will sort without having to set SortByColumn.
#”Added YearMonth” = Table.AddColumn(#”Added MonthNo”, “YearMonth”, each Date.ToText( [Date], [Format=”yyyy-“] )
& Text.Repeat(Character.FromNumber(8203), 12 – Date.Month([Date]))
& Text.Upper( Date.ToText( [Date], [Format=”MMM”] ) ), type text),
#”Added YearMonthNo” = Table.AddColumn(#”Added YearMonth”, “YearMonthNo”, each Date.ToText( [Date], [Format=”yyyy-MM”] ), type text),
// A zero width space is repeated at front of name so that name will sort without having to set SortByColumn
#”Added Weekday” = Table.AddColumn(#”Added YearMonthNo”, “Weekday”, each Text.Repeat(Character.FromNumber(8203), 6 – Date.DayOfWeek([Date]))
& Date.ToText( [Date], [Format=”dddd”] ), type text),
// A zero width space is repeated at front of name so that name will sort without having to set SortByColumn
#”Added WeekdayShort” = Table.AddColumn(#”Added Weekday”, “WeekdayShort”, each Text.Repeat(Character.FromNumber(8203), 6 – Date.DayOfWeek([Date]))
& Date.ToText( [Date], [Format=”ddd”] ), type text),
#”Added WeekdayNo” = Table.AddColumn(#”Added WeekdayShort”, “WeekdayNo”, each Number.ToText( Date.DayOfWeek(([Date]), Day.Sunday) + 1), type text)
in
#”Added WeekdayNo”