Use Case: There are many scenarios where we are required to extract the integer portion of a number.

Hence, for 23.57 and for -45.678, the integer portions will be 23 and -45 respectively. 

Power Query Number functions library doesn't provide any function which can do this. Hence, we will work out a formula to make a Number.Int function.

This is an article about specific transformation of split column values.

In many cases, it involves splitting the value. If it is just a basic splitting of the value, it can be achieved by using Split function in Power query. However, in the following case, the user has different values in each cell with "|" as a separator and needs to remove the separator and sort the values in reverse order based on the date after the separator is removed.

Use Case - Power Query's M language provides a rich collection of functions and its Date functions library offer a varieties of functions to perform multitude of date related tasks. One of the functions offered by Power Query's M language is Date.IsInYearToDate function. This function will give True or False depending on whether date is within Year to Date range. Hence, if today's date is 25-Mar-22, then dates from 1-Jan-22 to 25-Mar-22 will be giving True while any date not falling within this date range will give False. Hence, 26-Mar-22 will give the result of False.

Date.IsInYearToDate is a great function and finds tremendous use in handling many business scenario where you are asked to present things like YTD Sales, YTD Cost, YTD Profit, YTD Attrition and so on.

But Power Query's M Language doesn't have Date.IsInQuarterToDate, Date.IsInMonthToDate and Date.IsInWeekToDate functions which also have tremendous business needs. So, how to answer questions like QTD Sales, MTD Attrition, WTD Enquiries

Solution -  Following simple formulas can be used in this scenario

Date.IsInQuarterToDate

= Date.IsInCurrentQuarter(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())

Date.IsInMonthToDate

= Date.IsInCurrentMonth(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())

Date.IsInWeekToDate

= Date.IsInCurrentWeek(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())

In the above formulas, you will need to replace DateColumn with the column name against which you will be checking your dates.

The above formulas will work for all 3 date types - date, datetime, or datetimezone

To see the working of these, Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test. You can set TestYear to any year.  Currently I have set it to 2022.

let
    TestYear = 2022,
    ListOfDates = List.Dates(#date(TestYear,1,1), Duration.Days(#date(TestYear,12,31)-#date(TestYear,1,1))+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListOfDates, Splitter.SplitByNothing(), {"DateColumn"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"DateColumn", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date.IsInQuarterToDate", each Date.IsInCurrentQuarter(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date.IsInMonthToDate", each Date.IsInCurrentMonth(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date.IsInWeekToDate", each Date.IsInCurrentWeek(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow()))
in
    #"Added Custom2"

---- End of Article ----

Scenario:

Often people have problems with spaces in the abbreviations when importing data in Power BI Desktop. The sample data and the expected result are first provided below for the reader's reference.

This is a blog about how to use custom function with multiple condition logic to achieve switch in Power query.

Scenario:

In Power BI, we sometimes meet cumulative problem. For basic direct accumulation we could use Dax language or M language. But for conditional accumulation and group conditional accumulation, we tend to use M language. The following will be divided into three cases to gradually explore the cumulative summation.

Some users want to filter and summarize based on column names, however, some difficulties arise due to the fact that dax statements basically operate on the data in the specified column.

For example, there is a report card with only names and test takers' scores for each subject, the school wants to have a report that can be filtered using a slicer for each subject's score, how can this be achieved?

For some Power BI users, it is quite simple to calculate the difference between two rows by using DAX. And this article will provide you with two solutions about how to implement that in Power Query.

Scenario:

Suppose I have many columns which both column names and fields look untidy.  I want to do some transformations for them in batches. For example, replace multiple spaces as single space and uppercase each first character of word so that the table would look neater.

We often use specific value, ranges or some text functions to apply filter on data table fields. In fact, we can also use character functions to achieve some custom filter effect. Here I want to share some scenario about these function usages. 

Scenario

With my experience of using Power BI, many users want to insert a sum row into the data table instead of using the grand total feature in the table/matrix visual. However, Power BI does not support inputting rows into columns by DAX method. Therefore, I would like to introduce the way to insert rows in query editor with further extension of adding multiple rows to sum up the previous N days’ data. It is important to note that the method is only suitable if you would like to insert roles to the existing column. As the result, the date hierarchy would become invalid that may affect the creation of report, this is because adding the sum rows would change the column type from Date/Time to Text.

Scenario: 

I would like to get only one max value for each student ID. For example, a company needs to examine each person's attendance and identify the arrival time for being late monthly, but for the clarity of the report, it is required that if there is a duplication of the arrival time for being late, only one is identified.

Another example is a sales company that needs to mark the maximum monthly sales of each salesperson on the sales sheet and display the data during meetings. At this time, the maximum sales are duplicated, and if they are all marked, it will affect the beauty of the whole report, so only one of the duplicated maximum values should be randomly marked.

This blog will focus on getting you to learn how to calculate group distribution using Power Query in Power BI. This training is a relatively easy one. We’ll provide a dataset containing product and value and our goal will be to design a histogram to better communicate insights on the product base. 

Expand the continuous date interval (Excluding weekends and holidays)