Generation of custom Calendars in Power Query

In my previous post I described how to build relative date functions in Power Query

Associated topic is a generation of Calendar table for Excel Data Model (aka PowerPivot) where better to have only necessary period of time. And certain list of dates in some cases, e.g. ends of last X month if we analyse month results.

Assume that we have model with sales of previous month. Quite often in such model we don’t need dates of previous of future year and when we refresh such model in the beginning of new month our Calendar should be refreshed as well.

I took idea of calendar generation from post on http://powerpivotpro.com, thanks to Matt Allington.

Functions List.Dates and List.Generate can help us get initial list of dates and then extend it to useful calendar with long month names, short month names etc.


Relative dates and periods in Power Query

Last several months I have worked with OData protocol of SAP Business Bydesign using Power Query. Typical scenario: I pull data of different reports applying filters, then shape and load them to Excel 2013 data model and then do necessary calculations using DAX.

Very common task in such work is to define boundaries of report, what most often related to time frames. Some models require list of dates of last month, some – from start of last year to today, some year to date and so on.

Therefore, when query data using OData, I must generate strings like

“$filter=SOMEDATEFIELD ge datetime’yyyy-MM-ddT00:00:00′ and SOMEDATEFIELD le datetime’yyyy-MM-ddT00:00:00′

where value of ‘yyyy-MM-ddT00:00:00’ should be somehow calculated.

In SAP ByDesign standard reporting functional we have option to use relative selection in date-type fields. E.g. Invoice Date, Calendar Day, Year / Month, Year / Fiscal Period etc. In such fields we can use values like “Today”, “Tomorrow”, “Yesterday”, “Last day of Last Month”, “365 days ago” and so on.

I thought that it would be nice to have something similar in Power Query. Special library of Date.* functions that will extend current list of functions.

Define Today as

Date.Today = Date.From(DateTime.FixedLocalNow())

I expect that function DateTime.FixedLocalNow makes life of data model easier than DateTime.LocalNow. However, I did not any tests of productivity. Another note – I always change data type to Date using Date.From.

Our friends in this task are such functions as

Date.AddDays() Date.AddWeeks() Date.AddMonths() Date.AddQuaters() Date.AddYears()
Duration.Days() Date.StartOfWeek() Date.StartOfMonth() Date.StartOfQuarter() Date.StartOfYear()
Date.EndOfWeek() Date.EndOfMonth() Date.EndOfQuarter Date.EndOfYear()

Let’s start from simple

Date.Yesterday = Date.AddDays(Date.From(Date.Today), -1)

Date.Tomorrow = Date.AddDays(Date.From(Date.Today), 1)

Here I already use Date.Today instead of repeat Date.From(DateTime.FixedLocalNow()). This makes formula more convenient to read.

More common case – “N Days Ago” and “N Days in Future”

In Power Query (M) code we should define N.

N = 3

Date.NDaysAgo = Date.From(Date.AddDays(Date.Today, -N))

Date.NDaysInFuture = Date.From(Date.AddDays(Date.Today, N))

Let’s try something with months

Date.EndOfLastMonth = Date.From(Date.EndOfMonth(Date.AddMonths(Date.Today, -1)))

Date.StartOfMonth12MAgoExcCurMonth = Date.From(Date.StartOfMonth(Date.AddMonths(Date.Today, -12)))

Date.StartOfMonth12MAgoIncCurMonth = Date.From(Date.StartOfMonth(Date.AddMonths(Date.Today, -11)))

where Exc – excluding, Inc -including.

Again, we can easily make more common case – “End of month N months ago”

N = 3

Date.EndOfMonthNMonthsAgo = Date.From(Date.EndOfMonth(Date.AddMonths(Date.Today, -N)))

Something with years

Date.StartOfPreviousYear = Date.From(Date.AddYears(Date.StartOfYear(Date.Today), -1))

// or better to call it Date.StartOfLastYear

Date.StartOfCurrentYear = Date.From(Date.StartOfYear(Date.Today))

Idea is very simple – usage of combination of standard function. However, who wants to do repetitive work? Better to have library of checked and working functions.

More functions can be found in my repository on Github

Seek for Date.* and List.Dates.* functions.

Feel free to contribute your functions.

Dynamic Table Headers in Power Query (SAP ByDesign, Odata)

Another good example of how awesome is Power Query. And again I reference to one of well-known bloggers, Ken Puls’s post describes situation when we have pivoted data and need to unpivot them then rename columns. Fortunalety, in this example not so many columns used and unpivoting + join “rename table” is a solution.
However, in common case when we have table with 20 columns and 100 000 rows (or 2 million) unpivoting will be a very bad decision.
I faced with this challenge when I started to work with Odata protocol in SAP Business ByDesign.



Multiple Replacements of words in Power Query

Post of Chris Webb inspired me on creation of alternative way to make replacement in table column using separate table with list of Old & New values. Say, more scalable solution. Because List.Generate, used by Chris, and cell by cell replacement is very slow method.
Another Chris’s post helped me with it. The idea is to use recursion, go line by line of Replacement table and apply Table.ReplaceValue function.

After playing around with PQ I came to following function

ReplaceAll = (InputTable as table, ColumnName as text, ReplacementTable as table, optional StartRow as number) =>
ActualRow = if (StartRow =null) then 0 else StartRow,
result = Table.ReplaceValue(InputTable, ReplacementTable{ActualRow}[Old], ReplacementTable{ActualRow}[New] ,Replacer.ReplaceText, {ColumnName}),

NextRow = ActualRow + 1,

OutputTable = if NextRow > (Table.RowCount(ReplacementTable)-1)
then result
@ReplaceAll(result, ColumnName, ReplacementTable, NextRow)

Using this function I get same result
PQ Replacement

In addition, it performs replacement on thousands of rows in appropriate time.
You can download the sample workbook here.

Reporting and modeling for SAP Business ByDesign

Last two years I closely was working with SAP Business ByDesign reporting area. Have built data sources, web / Excel-based reports, models, print forms, broadcasts etc. In my responsibility six productive tenants: three of them in Baltics (Latvia, Lithuania, Estonia) and three in CIS (Kazakhstan, Ukraine, Belarus). I had to build starting package of reports and models to support legal requirements and business processes and worked on improvement of operational / strategic reports, consolidation of data on regional level etc. This post starts series about reporting for SAP Business ByDesign (further: SAP ByD or just ByD). I’m going to tell about problems I had to solve, some tricks and ideas. Hope this will help someone who is restricted in access rights as me and not an SAP ByD developer but going to building efficient reporting solution. So, let’s start. (more…)