Isolate Weekdays in Power BI Query

Extracting weekdays from dates is a vital process in knowledge evaluation, permitting for insightful visualizations and calculations. Energy BI Question, a robust device for knowledge transformation, affords a complete set of capabilities to isolate weekdays. By leveraging these capabilities, knowledge analysts can effortlessly extract weekday data from date columns, enabling them to realize useful insights into temporal patterns and tendencies. This text delves into the intricacies of isolating weekdays in Energy BI Question, offering a step-by-step information to empower knowledge professionals with the required information and strategies.

To provoke the method of isolating weekdays, it’s important to grasp the underlying knowledge construction. Energy BI Question represents dates as a mix of 12 months, month, day, hour, minute, and second parts. To extract the weekday, we are able to make the most of the Date.DayOfWeek() perform, which returns an integer representing the day of the week, the place 1 corresponds to Monday and seven corresponds to Sunday. This perform supplies a simple technique to categorize dates based mostly on their weekday affiliation.

Moreover, Energy BI Question affords extra capabilities that improve the pliability of weekday isolation. The Date.ToText() perform permits for the conversion of dates into textual content strings, enabling the extraction of particular date parts. By combining Date.ToText() with the Textual content.Extract() perform, knowledge analysts can isolate the weekday as a textual content string, offering higher versatility for downstream evaluation and reporting. This strategy empowers customers to tailor their knowledge transformations to fulfill the precise necessities of their analytical duties.

Filtering a Desk by Weekday in Energy BI Question

Energy BI Question, also referred to as Energy Question, is a robust device that means that you can hook up with, rework, and form knowledge from a variety of sources. One of many frequent duties when working with knowledge is to filter it based mostly on particular standards, such because the day of the week.

On this article, we’ll discover completely different strategies to isolate weekdays in Energy BI Question, offering step-by-step directions and examples for instance the method.

Utilizing the Date.DayOfWeek Operate

The Date.DayOfWeek perform returns the day of the week for a given date. It takes a datetime worth as enter and returns an integer representing the day of the week, the place 1 represents Sunday, 2 represents Monday, and so forth.

When utilized in a filter expression, the Date.DayOfWeek perform can be utilized to isolate particular weekdays. For instance, the next M code expression filters a desk to incorporate solely rows the place the date column falls on a Monday:

let   Supply = Desk.AddColumn(Supply, "Day of Week", every Date.DayOfWeek([Date Column])),
       FilteredTable = Desk.Filter(Supply, [Day of Week] = 2)
in
    FilteredTable

Utilizing the Date.IsInWeekdays Operate

The Date.IsInWeekdays perform returns a Boolean worth indicating whether or not a given date falls on a weekday (Monday by means of Friday). It takes a datetime worth as enter and returns TRUE if the date is a weekday, and FALSE in any other case.

Just like the Date.DayOfWeek perform, the Date.IsInWeekdays perform can be utilized in a filter expression to isolate weekdays. The next M code expression filters a desk to incorporate solely rows the place the date column falls on a weekday:

let   Supply = Desk.AddColumn(Supply, "Is Weekday", every Date.IsInWeekdays([Date Column])),
       FilteredTable = Desk.Filter(Supply, [Is Weekday] = true)
in
    FilteredTable

Utilizing a Customized Operate

For extra advanced situations, you possibly can create a customized perform to isolate weekdays. A customized perform means that you can outline your individual logic and standards for filtering the info.

The next M code defines a customized perform referred to as IsWeekday that returns TRUE if the date column falls on a weekday, and FALSE in any other case:

(date) as logical =
let
    dayOfWeek = Date.DayOfWeek(date),
    isWeekday = dayOfWeek >= 2 and dayOfWeek <= 6
in
    isWeekday

You’ll be able to then use this tradition perform in a filter expression to isolate weekdays:

let   Supply = Desk.AddColumn(Supply, "Is Weekday", every IsWeekday([Date Column])),
       FilteredTable = Desk.Filter(Supply, [Is Weekday] = true)
in
    FilteredTable

Utilizing a Calculated Desk

An alternative choice for isolating weekdays is to create a calculated desk that incorporates solely the weekdays from the unique desk.

To create a calculated desk, right-click on the desk within the Energy BI Question Editor and choose “New Calculated Desk.” Within the formulation bar, enter the next M code expression:

Weekdays = FILTER(
    ALL(Supply),
    Date.IsInWeekdays([Date Column])
)

This expression will create a brand new desk referred to as Weekdays that incorporates solely the rows the place the date column falls on a weekday.

Utilizing a Parameter Desk

If you wish to dynamically filter the desk by weekday, you need to use a parameter desk. A parameter desk means that you can specify an inventory of weekdays that you simply wish to filter by.

To create a parameter desk, create a brand new desk with two columns: Day and Worth. The Day column ought to include the names of the weekdays, and the Worth column ought to include the values that you simply wish to use for filtering.

For instance, the next desk defines a parameter desk that incorporates the weekdays Monday by means of Friday:

Day	Worth
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5

You’ll be able to then use the parameter desk in a filter expression to isolate particular weekdays. The next M code expression filters a desk to incorporate solely rows the place the date column falls on a day that’s included within the parameter desk:

let   Supply = Desk.AddColumn(Supply, "Day of Week", every Date.DayOfWeek([Date Column])),
       ParameterTable = Desk.FromRows(json, [#"Day" = type text, #"Value" = type number]),
       FilteredTable = Desk.Filter(Supply, Checklist.Comprises(ParameterTable[Day], [Day of Week]))
in
    FilteredTable

Making a Weekday Desk for Flexibility

The weekday desk supplies a flexible basis to your Energy BI queries, permitting you to effortlessly isolate weekdays, manipulate dates, and carry out superior date-related calculations. By establishing a devoted weekday desk, you acquire higher management over your knowledge evaluation and reporting.

To create a weekday desk:

In Energy BI Desktop, navigate to the “Rework” tab.
Choose “New Supply” > “Clean Question”.
Enter the next formulation within the formulation bar:

= Desk.FromList({{"Monday"},{"Tuesday"},{"Wednesday"},{"Thursday"},{"Friday"},{"Saturday"},{"Sunday"}})

Click on “Enter” to create your weekday desk.

This desk will function a reference for weekdays, permitting you to carry out versatile date calculations and filtering. By leveraging the WEEKDAY perform or the M perform Date.DayOfWeek, you possibly can extract the weekday quantity or title from any date column.

For instance, to isolate weekdays from a date column named “Date”, you need to use the next formulation:

= Desk.AddColumn(#"Your Desk", "Weekday", every Date.DayOfWeek(#"Date"))

It will create a brand new column named “Weekday” that incorporates the weekday numbers for every date within the unique desk. You’ll be able to then filter or group by the “Weekday” column to research knowledge particular to sure days of the week.

Superior Date Calculations and Filtering

The weekday desk empowers you to carry out superior date calculations and filtering. As an illustration, you possibly can simply calculate the variety of days between two dates or decide the final enterprise day of a month. By leveraging DAX capabilities comparable to DATEADD and EOMONTH, you possibly can manipulate dates with ease.

Moreover, you need to use the weekday desk to filter knowledge based mostly on particular weekdays or date ranges. This flexibility means that you can tailor your evaluation to fulfill your particular reporting wants. As an illustration, you possibly can isolate weekend knowledge or exclude holidays out of your calculations.

Desk Construction

The weekday desk, by default, has the next construction:

Column Title	Information Kind	Description
Weekday	Textual content	The title of the weekday

You’ll be able to customise the desk construction to fulfill your particular necessities. As an illustration, you possibly can add extra columns to seize the weekday quantity or the ISO weekday quantity. The flexibleness of the weekday desk means that you can tailor it to your distinctive knowledge evaluation wants.

Conclusion

A weekday desk is a robust device that enhances the pliability and analytical capabilities of Energy BI queries. By making a devoted weekday desk, you acquire management over date manipulation, filtering, and superior date calculations. This versatility allows you to extract significant insights out of your knowledge and generate tailor-made studies that meet your particular enterprise necessities.

Merging Tables Primarily based on Weekday Standards

Weekdays Desk

To create a weekdays desk, you need to use the next M code:

“`
let
Supply = Desk.FromList({{“Monday”},{“Tuesday”},{“Wednesday”},{“Thursday”},{“Friday”}}, Splitter.SplitByNothing()),
#”Added Customized Column” = Desk.AddColumn(Supply, “DayNumber”, every _, sort quantity)
in
#”Added Customized Column”
“`

It will create a desk with two columns: “Day” and “DayNumber”. The “DayNumber” column incorporates the day quantity, with 1 representing Monday and seven representing Sunday.

Date Desk

To create a date desk, you need to use the next M code:

“`
let
Supply = Calendar.Present(),
#”Added Customized Column” = Desk.AddColumn(Supply, “DayName”, every Date.ToText(_, “dddd”), sort textual content),
#”Added Customized Column1″ = Desk.AddColumn(#”Added Customized Column”, “DayNumber”, every Date.DayOfWeek(_, Day.Monday), sort quantity)
in
#”Added Customized Column1″
“`

It will create a desk with 4 columns: “Date”, “DayName”, “DayNumber”, and “MonthName”. The “DayNumber” column incorporates the day quantity, with 1 representing Monday and seven representing Sunday.

Merging Tables

To merge the weekdays desk and the date desk, you need to use the next M code:

“`
let
WeekdaysTable = Desk.FromList({{“Monday”},{“Tuesday”},{“Wednesday”},{“Thursday”},{“Friday”}}, Splitter.SplitByNothing()),
DateTable = Calendar.Present(),
#”Added Customized Column” = Desk.AddColumn(DateTable, “DayName”, every Date.ToText(_, “dddd”), sort textual content),
#”Added Customized Column1″ = Desk.AddColumn(#”Added Customized Column”, “DayNumber”, every Date.DayOfWeek(_, Day.Monday), sort quantity),
#”Merged Queries” = Desk.NestedJoin(#”Added Customized Column1″, {“DayNumber”}, WeekdaysTable, {“DayNumber”}, “Weekdays”, JoinKind.LeftOuter),
#”Expanded Weekdays” = Desk.ExpandTableColumn(#”Merged Queries”, “Weekdays”, {“Day”}, {“Day”})
in
#”Expanded Weekdays”
“`

It will create a desk with 5 columns: “Date”, “DayName”, “DayNumber”, “MonthName”, and “Day”. The “Day” column incorporates the weekday title, comparable to “Monday” or “Tuesday”.

Utilizing the Merged Desk

The merged desk can be utilized to filter knowledge based mostly on weekday standards. For instance, the next M code will create a desk with solely the rows for Monday:

It will create a desk with one column: “Day”. The desk will include solely the row for Monday.

Changing Weekdays to ISO Format

Encoding Weekdays in ISO 8601

The ISO 8601 commonplace defines a constant format for representing dates and instances throughout completely different techniques and cultures. On this commonplace, weekdays are encoded utilizing numeric codes starting from 1 to 7, the place 1 represents Monday and seven represents Sunday.

Syntax

For instance, the weekday Monday could be encoded as "1" in ISO 8601 format. The next desk summarizes the ISO 8601 weekday codes:

Day	ISO Code
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5
Saturday	6
Sunday	7

Changing Weekday Textual content to ISO Code

To transform a weekday string (e.g., "Monday") to its corresponding ISO code, you need to use the next formulation in Energy BI Question:

= 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))

Substitute the dates with the precise dates of the weekdays you wish to convert.

This formulation makes use of the Date.FromText perform to transform the weekday string to a date worth. It then checks the day of the week for the transformed date and returns the corresponding ISO code.

Instance

Take into account the next instance dataset:

To transform the weekdays on this dataset to ISO codes, you possibly can apply the aforementioned formulation to the "Weekday" column. The ensuing dataset will appear to be this:

Weekday	ISO Code
Monday	1
Tuesday	2
Wednesday	3
Thursday	4
Friday	5

By changing weekdays to ISO format, you possibly can guarantee constant and standardized date and time representations in your Energy BI queries and studies.

Utilizing Energy BI DAX to Improve Weekday Calculations

WEEKDAY Operate

The WEEKDAY perform returns the day of the week for a given date. The syntax for the WEEKDAY perform is as follows:

WEEKDAY(date, [return_type])

The date parameter is the date for which you wish to return the day of the week. The return_type parameter is non-compulsory, and it specifies the format of the returned worth. The return_type parameter may be one of many following values:

1 – Returns the day of the week as a quantity (1 for Sunday, 2 for Monday, and so forth).

2 – Returns the day of the week as a string (“Sunday”, “Monday”, and so forth).

3 – Returns the day of the week as an integer (0 for Sunday, 1 for Monday, and so forth).

If the return_type parameter shouldn’t be specified, the WEEKDAY perform returns the day of the week as a quantity.

For instance, the next formulation returns the day of the week for the date “2023-03-08”:

WEEKDAY(“2023-03-08”)

The results of this formulation is 3, which represents Wednesday.

SWITCH Operate

The SWITCH perform means that you can consider a number of situations and return a distinct worth for every situation. The syntax for the SWITCH perform is as follows:

SWITCH(expression, value1, result1, value2, result2, …, default)

The expression parameter is the worth that you simply wish to consider. The value1, value2, …, valueN parameters are the values that you simply wish to examine the expression to. The result1, result2, …, resultN parameters are the values that you simply wish to return if the expression matches the corresponding worth. The default parameter is the worth that you simply wish to return if the expression doesn’t match any of the values.

For instance, the next formulation makes use of the SWITCH perform 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 results of this formulation is “Wednesday”.

IF Operate

The IF perform means that you can consider a situation and return a distinct worth relying on whether or not the situation is true or false. The syntax for the IF perform is as follows:

IF(logical_test, value_if_true, value_if_false)

The logical_test parameter is the situation that you simply wish to consider. The value_if_true parameter is the worth that you simply wish to return if the situation is true. The value_if_false parameter is the worth that you simply wish to return if the situation is fake.

For instance, the next formulation makes use of the IF perform 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 results of this formulation is “Wednesday”.

FORMAT Operate

The FORMAT perform means that you can format a price as a string. The syntax for the FORMAT perform is as follows:

FORMAT(worth, format_string)

The worth parameter is the worth that you simply wish to format. The format_string parameter is the format that you simply wish to apply to the worth.

For instance, the next formulation makes use of the FORMAT perform to format the date “2023-03-08” as a string:

FORMAT(“2023-03-08”, “dddd”)

The results of this formulation is “Wednesday”.

TEXTJOIN Operate

The TEXTJOIN perform means that you can concatenate a number of values right into a single string. The syntax for the TEXTJOIN perform is as follows:

TEXTJOIN(delimiter, ignore_empty, text1, text2, …, textN)

The delimiter parameter is the character that you simply wish to use to separate the values. The ignore_empty parameter is a Boolean worth that specifies whether or not or to not ignore empty values. The text1, text2, …, textN parameters are the values that you simply wish to concatenate.

For instance, the next formulation makes use of the TEXTJOIN perform to concatenate the times of the week right into a single string:

TEXTJOIN(“, “, FALSE, “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)

The results of this formulation is “Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday”.

CONCATENATE Operate

The CONCATENATE perform means that you can concatenate a number of values right into a single string. The syntax for the CONCATENATE perform is as follows:

CONCATENATE(text1, text2, …, textN)

The text1, text2, …, textN parameters are the values that you simply wish to concatenate.

For instance, the next formulation makes use of the CONCATENATE perform to concatenate the times of the week right into a single string:

CONCATENATE(“Sunday”, “, “, “Monday”, “, “, “Tuesday”, “, “, “Wednesday”, “, “, “Thursday”, “, “, “Friday”, “, “, “Saturday”)

The results of this formulation is “Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday”.

MID Operate

The MID perform means that you can extract a substring from a string. The syntax for the MID perform is as follows:

MID(textual content, start_num, num_chars)

The textual content parameter is the string from which you wish to extract the substring. The start_num parameter is the beginning place of the substring. The num_chars parameter is the variety of characters to extract.

For instance, the next formulation makes use of the MID perform to extract the day of the week from a date string:

MID(“2023-03-08”, 9, 2)

The results of this formulation is “08”.

LEFT Operate

The LEFT perform means that you can extract a substring from the left facet of a string. The syntax for the LEFT perform is as follows:

LEFT(textual content, num_chars)

The textual content parameter is the string from which you wish to extract the substring. The num_chars parameter is the variety of characters to extract.

For instance, the next formulation makes use of the LEFT perform to extract the day of the week from a date string:

LEFT(“2023-03-08”, 3)

The results of this formulation is “202”.

RIGHT Operate

The RIGHT perform means that you can extract a substring from the fitting facet of a string. The syntax for the RIGHT perform is as follows:

RIGHT(textual content, num_chars)

The textual content parameter is the string from which you wish to extract the substring. The num_chars parameter is the variety of characters to extract.

For instance, the next formulation makes use of the RIGHT perform to extract the day of the week from a date string:

RIGHT(“2023-03-08”, 2)

The results of this formulation is “08”.

Weekdays Desk

Day	Worth
Sunday	1
Monday	2
Tuesday	3
Wednesday	4
Thursday	5
Friday	6
Saturday	7

121. Methods to Isolate Weekdays in Energy BI Question

Overview

The Energy BI Question Editor supplies highly effective instruments for manipulating and remodeling knowledge. One frequent process is to isolate weekdays from a date column. This may be helpful for creating visualizations or performing calculations that target particular days of the week.

Step-by-Step Directions

To isolate weekdays in Energy BI Question, observe these steps:

Open the Energy BI Question Editor by deciding on "Rework Information" from the "House" tab in Energy BI Desktop.
Choose the Date column you want to rework.
Click on on the "Add Column" tab within the Question Editor ribbon.
Choose "Customized Column" from the drop-down menu.
Within the "Customized Column Formulation" dialog field, enter the next formulation:

= Date.DayName([Date])

the place [Date] is the title of your Date column.

Click on "OK" to create the brand new column.
The brand new column will show the weekday names for every date within the chosen column.

Individuals Additionally Ask

How do I filter by particular weekdays in Energy BI Question?

To filter by particular weekdays in Energy BI Question, create a customized filter utilizing the Weekday() perform:

= Weekday([Date]) = "Monday"

the place [Date] is the title of your Date column.

How can I take away weekends from a date desk in Energy BI Question?

To take away weekends from a date desk in Energy BI Question, create a calculated column utilizing the next formulation:

= IF(Weekday([Date]) = 1 || Weekday([Date]) = 7, BLANK(), [Date])

the place [Date] is the title of your Date column.