Extracting weekdays from dates is a crucial task in data analysis, allowing for insightful visualizations and calculations. Power BI Query, a powerful tool for data transformation, offers a comprehensive set of functions to isolate weekdays. By leveraging these functions, data analysts can effortlessly extract weekday information from date columns, enabling them to gain valuable insights into temporal patterns and trends. This article delves into the intricacies of isolating weekdays in Power BI Query, providing a step-by-step guide to empower data professionals with the necessary knowledge and techniques.
To initiate the process of isolating weekdays, it is essential to understand the underlying data structure. Power BI Query represents dates as a combination of year, month, day, hour, minute, and second components. To extract the weekday, we can utilize the Date.DayOfWeek() function, which returns an integer representing the day of the week, where 1 corresponds to Monday and 7 corresponds to Sunday. This function provides a straightforward method to categorize dates based on their weekday affiliation.
Furthermore, Power BI Query offers additional functions that enhance the flexibility of weekday isolation. The Date.ToText() function allows for the conversion of dates into text strings, enabling the extraction of specific date components. By combining Date.ToText() with the Text.Extract() function, data analysts can isolate the weekday as a text string, providing greater versatility for downstream analysis and reporting. This approach empowers users to tailor their data transformations to meet the specific requirements of their analytical tasks.
Filtering a Table by Weekday in Power BI Query
Power BI Query, also known as Power Query, is a powerful tool that allows you to connect to, transform, and shape data from a wide range of sources. One of the common tasks when working with data is to filter it based on specific criteria, such as the day of the week.
In this article, we will explore different methods to isolate weekdays in Power BI Query, providing step-by-step instructions and examples to illustrate the process.
Using the Date.DayOfWeek Function
The Date.DayOfWeek function returns the day of the week for a given date. It takes a datetime value as input and returns an integer representing the day of the week, where 1 represents Sunday, 2 represents Monday, and so on.
When used in a filter expression, the Date.DayOfWeek function can be used to isolate specific weekdays. For example, the following M code expression filters a table to include only rows where the date column falls on a Monday:
let Source = Table.AddColumn(Source, "Day of Week", each Date.DayOfWeek([Date Column])),
FilteredTable = Table.Filter(Source, [Day of Week] = 2)
in
FilteredTable
Using the Date.IsInWeekdays Function
The Date.IsInWeekdays function returns a Boolean value indicating whether a given date falls on a weekday (Monday through Friday). It takes a datetime value as input and returns TRUE if the date is a weekday, and FALSE otherwise.
Similar to the Date.DayOfWeek function, the Date.IsInWeekdays function can be used in a filter expression to isolate weekdays. The following M code expression filters a table to include only rows where the date column falls on a weekday:
let Source = Table.AddColumn(Source, "Is Weekday", each Date.IsInWeekdays([Date Column])),
FilteredTable = Table.Filter(Source, [Is Weekday] = true)
in
FilteredTable
Using a Custom Function
For more complex scenarios, you can create a custom function to isolate weekdays. A custom function allows you to define your own logic and criteria for filtering the data.
The following M code defines a custom function called IsWeekday that returns TRUE if the date column falls on a weekday, and FALSE otherwise:
(date) as logical =
let
dayOfWeek = Date.DayOfWeek(date),
isWeekday = dayOfWeek >= 2 and dayOfWeek <= 6
in
isWeekday
You can then use this custom function in a filter expression to isolate weekdays:
let Source = Table.AddColumn(Source, "Is Weekday", each IsWeekday([Date Column])),
FilteredTable = Table.Filter(Source, [Is Weekday] = true)
in
FilteredTable
Using a Calculated Table
Another option for isolating weekdays is to create a calculated table that contains only the weekdays from the original table.
To create a calculated table, right-click on the table in the Power BI Query Editor and select “New Calculated Table.” In the formula bar, enter the following M code expression:
Weekdays = FILTER(
ALL(Source),
Date.IsInWeekdays([Date Column])
)
This expression will create a new table called Weekdays that contains only the rows where the date column falls on a weekday.
Using a Parameter Table
If you want to dynamically filter the table by weekday, you can use a parameter table. A parameter table allows you to specify a list of weekdays that you want to filter by.
To create a parameter table, create a new table with two columns: Day and Value. The Day column should contain the names of the weekdays, and the Value column should contain the values that you want to use for filtering.
For example, the following table defines a parameter table that contains the weekdays Monday through Friday:
| Day | Value |
|---|---|
| Monday | 1 |
| Tuesday | 2 |
| Wednesday | 3 |
| Thursday | 4 |
| Friday | 5 |
You can then use the parameter table in a filter expression to isolate specific weekdays. The following M code expression filters a table to include only rows where the date column falls on a day that is included in the parameter table:
let Source = Table.AddColumn(Source, "Day of Week", each Date.DayOfWeek([Date Column])),
ParameterTable = Table.FromRows(json, [#"Day" = type text, #"Value" = type number]),
FilteredTable = Table.Filter(Source, List.Contains(ParameterTable[Day], [Day of Week]))
in
FilteredTable
Creating a Weekday Table for Flexibility
The weekday table provides a versatile foundation for your Power BI queries, allowing you to effortlessly isolate weekdays, manipulate dates, and perform advanced date-related calculations. By constructing a dedicated weekday table, you gain greater control over your data analysis and reporting.
To create a weekday table:
- In Power BI Desktop, navigate to the “Transform” tab.
- Select “New Source” > “Blank Query”.
- Enter the following formula in the formula bar:
- Click “Enter” to create your weekday table.
= Table.FromList({{"Monday"},{"Tuesday"},{"Wednesday"},{"Thursday"},{"Friday"},{"Saturday"},{"Sunday"}})
This table will serve as a reference for weekdays, allowing you to perform flexible date calculations and filtering. By leveraging the WEEKDAY function or the M function Date.DayOfWeek, you can extract the weekday number or name from any date column.
For example, to isolate weekdays from a date column named “Date”, you can use the following formula:
= Table.AddColumn(#"Your Table", "Weekday", each Date.DayOfWeek(#"Date"))
This will create a new column named “Weekday” that contains the weekday numbers for each date in the original table. You can then filter or group by the “Weekday” column to analyze data specific to certain days of the week.
Advanced Date Calculations and Filtering
The weekday table empowers you to perform advanced date calculations and filtering. For instance, you can easily calculate the number of days between two dates or determine the last business day of a month. By leveraging DAX functions such as DATEADD and EOMONTH, you can manipulate dates with ease.
Additionally, you can use the weekday table to filter data based on specific weekdays or date ranges. This flexibility allows you to tailor your analysis to meet your specific reporting needs. For instance, you can isolate weekend data or exclude holidays from your calculations.
Table Structure
The weekday table, by default, has the following structure:
| Column Name | Data Type | Description |
|---|---|---|
| Weekday | Text | The name of the weekday |
You can customize the table structure to meet your specific requirements. For instance, you can add additional columns to capture the weekday number or the ISO weekday number. The flexibility of the weekday table allows you to tailor it to your unique data analysis needs.
Conclusion
A weekday table is a powerful tool that enhances the flexibility and analytical capabilities of Power BI queries. By creating a dedicated weekday table, you gain control over date manipulation, filtering, and advanced date calculations. This versatility enables you to extract meaningful insights from your data and generate tailored reports that meet your specific business requirements.
Merging Tables Based on Weekday Criteria
Weekdays Table
To create a weekdays table, you can use the following M code:
“`
let
Source = Table.FromList({{“Monday”},{“Tuesday”},{“Wednesday”},{“Thursday”},{“Friday”}}, Splitter.SplitByNothing()),
#”Added Custom Column” = Table.AddColumn(Source, “DayNumber”, each _, type number)
in
#”Added Custom Column”
“`
This will create a table with two columns: “Day” and “DayNumber”. The “DayNumber” column contains the day number, with 1 representing Monday and 7 representing Sunday.
Date Table
To create a date table, you can use the following M code:
“`
let
Source = Calendar.Current(),
#”Added Custom Column” = Table.AddColumn(Source, “DayName”, each Date.ToText(_, “dddd”), type text),
#”Added Custom Column1″ = Table.AddColumn(#”Added Custom Column”, “DayNumber”, each Date.DayOfWeek(_, Day.Monday), type number)
in
#”Added Custom Column1″
“`
This will create a table with four columns: “Date”, “DayName”, “DayNumber”, and “MonthName”. The “DayNumber” column contains the day number, with 1 representing Monday and 7 representing Sunday.
Merging Tables
To merge the weekdays table and the date table, you can use the following M code:
“`
let
WeekdaysTable = Table.FromList({{“Monday”},{“Tuesday”},{“Wednesday”},{“Thursday”},{“Friday”}}, Splitter.SplitByNothing()),
DateTable = Calendar.Current(),
#”Added Custom Column” = Table.AddColumn(DateTable, “DayName”, each Date.ToText(_, “dddd”), type text),
#”Added Custom Column1″ = Table.AddColumn(#”Added Custom Column”, “DayNumber”, each Date.DayOfWeek(_, Day.Monday), type number),
#”Merged Queries” = Table.NestedJoin(#”Added Custom Column1″, {“DayNumber”}, WeekdaysTable, {“DayNumber”}, “Weekdays”, JoinKind.LeftOuter),
#”Expanded Weekdays” = Table.ExpandTableColumn(#”Merged Queries”, “Weekdays”, {“Day”}, {“Day”})
in
#”Expanded Weekdays”
“`
This will create a table with five columns: “Date”, “DayName”, “DayNumber”, “MonthName”, and “Day”. The “Day” column contains the weekday name, such as “Monday” or “Tuesday”.
Using the Merged Table
The merged table can be used to filter data based on weekday criteria. For example, the following M code will create a table with only the rows for Monday:
“`
let
Source = Table.FromList({{“Monday”},{“Tuesday”},{“Wednesday”},{“Thursday”},{“Friday”}}, Splitter.SplitByNothing()),
#”Added Custom Column” = Table.AddColumn(Source, “DayNumber”, each _, type number),
#”Filtered Rows” = Table.SelectRows(#”Added Custom Column”, each _[DayNumber] = 1)
in
#”Filtered Rows”
“`
This will create a table with one column: “Day”. The table will contain only the row for Monday.
Converting Weekdays to ISO Format
Encoding Weekdays in ISO 8601
The ISO 8601 standard defines a consistent format for representing dates and times across different systems and cultures. In this standard, weekdays are encoded using numeric codes ranging from 1 to 7, where 1 represents Monday and 7 represents Sunday.
Syntax
For example, the weekday Monday would be encoded as "1" in ISO 8601 format. The following table summarizes the ISO 8601 weekday codes:
| Day | ISO Code |
|---|---|
| Monday | 1 |
| Tuesday | 2 |
| Wednesday | 3 |
| Thursday | 4 |
| Friday | 5 |
| Saturday | 6 |
| Sunday | 7 |
Converting Weekday Text to ISO Code
To convert a weekday string (e.g., "Monday") to its corresponding ISO code, you can use the following formula in Power BI Query:
= if Date.FromText([Weekday]) = #date(2023, 1, 16), 1,
if Date.FromText([Weekday]) = #date(2023, 1, 17), 2,
if Date.FromText([Weekday]) = #date(2023, 1, 18), 3,
if Date.FromText([Weekday]) = #date(2023, 1, 19), 4,
if Date.FromText([Weekday]) = #date(2023, 1, 20), 5,
if Date.FromText([Weekday]) = #date(2023, 1, 21), 6,
if Date.FromText([Weekday]) = #date(2023, 1, 22), 7))
Replace the dates with the actual dates of the weekdays you want to convert.
This formula uses the Date.FromText function to convert the weekday string to a date value. It then checks the day of the week for the converted date and returns the corresponding ISO code.
Example
Consider the following example dataset:
| Weekday |
|—|—|
| Monday |
| Tuesday |
| Wednesday |
| Thursday |
| Friday |
To convert the weekdays in this dataset to ISO codes, you can apply the aforementioned formula to the "Weekday" column. The resulting dataset will look like this:
| Weekday | ISO Code |
|---|---|
| Monday | 1 |
| Tuesday | 2 |
| Wednesday | 3 |
| Thursday | 4 |
| Friday | 5 |
By converting weekdays to ISO format, you can ensure consistent and standardized date and time representations in your Power BI queries and reports.
Using Power BI DAX to Enhance Weekday Calculations
WEEKDAY Function
The WEEKDAY function returns the day of the week for a given date. The syntax for the WEEKDAY function is as follows:
WEEKDAY(date, [return_type])
The date parameter is the date for which you want to return the day of the week. The return_type parameter is optional, and it specifies the format of the returned value. The return_type parameter can be one of the following values:
1 – Returns the day of the week as a number (1 for Sunday, 2 for Monday, and so on).
2 – Returns the day of the week as a string (“Sunday”, “Monday”, and so on).
3 – Returns the day of the week as an integer (0 for Sunday, 1 for Monday, and so on).
If the return_type parameter is not specified, the WEEKDAY function returns the day of the week as a number.
For example, the following formula returns the day of the week for the date “2023-03-08”:
WEEKDAY(“2023-03-08”)
The result of this formula is 3, which represents Wednesday.
SWITCH Function
The SWITCH function allows you to evaluate multiple conditions and return a different value for each condition. The syntax for the SWITCH function is as follows:
SWITCH(expression, value1, result1, value2, result2, …, default)
The expression parameter is the value that you want to evaluate. The value1, value2, …, valueN parameters are the values that you want to compare the expression to. The result1, result2, …, resultN parameters are the values that you want to return if the expression matches the corresponding value. The default parameter is the value that you want to return if the expression does not match any of the values.
For example, the following formula uses the SWITCH function to return the day of the week for a given date:
SWITCH(WEEKDAY(“2023-03-08”), 1, “Sunday”, 2, “Monday”, 3, “Tuesday”, 4, “Wednesday”, 5, “Thursday”, 6, “Friday”, 7, “Saturday”)
The result of this formula is “Wednesday”.
IF Function
The IF function allows you to evaluate a condition and return a different value depending on whether the condition is true or false. The syntax for the IF function is as follows:
IF(logical_test, value_if_true, value_if_false)
The logical_test parameter is the condition that you want to evaluate. The value_if_true parameter is the value that you want to return if the condition is true. The value_if_false parameter is the value that you want to return if the condition is false.
For example, the following formula uses the IF function to return the day of the week for a given date:
IF(WEEKDAY(“2023-03-08”) = 1, “Sunday”, IF(WEEKDAY(“2023-03-08”) = 2, “Monday”, IF(WEEKDAY(“2023-03-08”) = 3, “Tuesday”, IF(WEEKDAY(“2023-03-08”) = 4, “Wednesday”, IF(WEEKDAY(“2023-03-08”) = 5, “Thursday”, IF(WEEKDAY(“2023-03-08”) = 6, “Friday”, “Saturday”))))))
The result of this formula is “Wednesday”.
FORMAT Function
The FORMAT function allows you to format a value as a string. The syntax for the FORMAT function is as follows:
FORMAT(value, format_string)
The value parameter is the value that you want to format. The format_string parameter is the format that you want to apply to the value.
For example, the following formula uses the FORMAT function to format the date “2023-03-08” as a string:
FORMAT(“2023-03-08”, “dddd”)
The result of this formula is “Wednesday”.
TEXTJOIN Function
The TEXTJOIN function allows you to concatenate multiple values into a single string. The syntax for the TEXTJOIN function is as follows:
TEXTJOIN(delimiter, ignore_empty, text1, text2, …, textN)
The delimiter parameter is the character that you want to use to separate the values. The ignore_empty parameter is a Boolean value that specifies whether or not to ignore empty values. The text1, text2, …, textN parameters are the values that you want to concatenate.
For example, the following formula uses the TEXTJOIN function to concatenate the days of the week into a single string:
TEXTJOIN(“, “, FALSE, “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)
The result of this formula is “Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday”.
CONCATENATE Function
The CONCATENATE function allows you to concatenate multiple values into a single string. The syntax for the CONCATENATE function is as follows:
CONCATENATE(text1, text2, …, textN)
The text1, text2, …, textN parameters are the values that you want to concatenate.
For example, the following formula uses the CONCATENATE function to concatenate the days of the week into a single string:
CONCATENATE(“Sunday”, “, “, “Monday”, “, “, “Tuesday”, “, “, “Wednesday”, “, “, “Thursday”, “, “, “Friday”, “, “, “Saturday”)
The result of this formula is “Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday”.
MID Function
The MID function allows you to extract a substring from a string. The syntax for the MID function is as follows:
MID(text, start_num, num_chars)
The text parameter is the string from which you want to extract the substring. The start_num parameter is the starting position of the substring. The num_chars parameter is the number of characters to extract.
For example, the following formula uses the MID function to extract the day of the week from a date string:
MID(“2023-03-08”, 9, 2)
The result of this formula is “08”.
LEFT Function
The LEFT function allows you to extract a substring from the left side of a string. The syntax for the LEFT function is as follows:
LEFT(text, num_chars)
The text parameter is the string from which you want to extract the substring. The num_chars parameter is the number of characters to extract.
For example, the following formula uses the LEFT function to extract the day of the week from a date string:
LEFT(“2023-03-08”, 3)
The result of this formula is “202”.
RIGHT Function
The RIGHT function allows you to extract a substring from the right side of a string. The syntax for the RIGHT function is as follows:
RIGHT(text, num_chars)
The text parameter is the string from which you want to extract the substring. The num_chars parameter is the number of characters to extract.
For example, the following formula uses the RIGHT function to extract the day of the week from a date string:
RIGHT(“2023-03-08”, 2)
The result of this formula is “08”.
Weekdays Table
| Day | Value |
|---|---|
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
121. How to Isolate Weekdays in Power BI Query
Overview
The Power BI Query Editor provides powerful tools for manipulating and transforming data. One common task is to isolate weekdays from a date column. This can be useful for creating visualizations or performing calculations that focus on specific days of the week.
Step-by-Step Instructions
To isolate weekdays in Power BI Query, follow these steps:
- Open the Power BI Query Editor by selecting "Transform Data" from the "Home" tab in Power BI Desktop.
- Select the Date column you wish to transform.
- Click on the "Add Column" tab in the Query Editor ribbon.
- Select "Custom Column" from the drop-down menu.
- In the "Custom Column Formula" dialog box, enter the following formula:
= Date.DayName([Date])
where [Date] is the name of your Date column.
- Click "OK" to create the new column.
- The new column will display the weekday names for each date in the selected column.
People Also Ask
How do I filter by specific weekdays in Power BI Query?
To filter by specific weekdays in Power BI Query, create a custom filter using the Weekday() function:
= Weekday([Date]) = "Monday"
where [Date] is the name of your Date column.
How can I remove weekends from a date table in Power BI Query?
To remove weekends from a date table in Power BI Query, create a calculated column using the following formula:
= IF(Weekday([Date]) = 1 || Weekday([Date]) = 7, BLANK(), [Date])
where [Date] is the name of your Date column.
