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

https://github.com/IvanBond/pquery

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.

Using Odata we have to provide string with list of fields ID in $select, for example query of Account Contact Data:

https://myXXXXXX.sapbydesign.com/sap/byd/odata/cc_home_analytics.svc/RPBPCSCONTB_Q0001QueryResults? $top=100000&$select=CBO_ROLE_CODE,CBP_STATUS_CODE,CBP_UUID,CCONT_FUNCTIONAL_AREA_CODE, CCONT_FUNCTION_TYPE_CODE,CCONT_MAIN_INDICATOR,CCONT_UUID,CCONT_WPA_BUILDING,CCONT_WPA_DEPARTMENT_NAME, CCONT_WPA_EMAIL_CONTENT,CCONT_WPA_FAX_NUMBER,CCONT_WPA_FLOOR,CCONT_WPA_FUNCTIONAL_TITLE, CCONT_WPA_MOBILE_NUMBER, CCONT_WPA_PHONE_NUMBER,CCONT_WPA_POSTAL_ADR_DESCR,CCONT_WPA_PREF_COMMU_MEDIUM, CCONT_WPA_ROOM_ID,CCURR_EMPL_RESP_PARTY_ROLE_CODE,CCURR_EMPL_RESP_UUID_CONTENT,CDEFADDR_FORM_POSTAL_ADDR, FK0COUNT, KK0COUNT,TBO_ROLE_CODE,TBP_STATUS_CODE,TBP_UUID,TCONT_FUNCTIONAL_AREA_CODE, TCONT_FUNCTION_TYPE_CODE, TCONT_UUID,TCONT_WPA_PREF_COMMU_MEDIUM,TCURR_EMPL_RESP_PARTY_ROLE_CODE, TCURR_EMPL_RESP_UUID_CONTENT& $format=json

(more…)

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

let
ReplaceAll = (InputTable as table, ColumnName as text, ReplacementTable as table, optional StartRow as number) =>
let
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
else
@ReplaceAll(result, ColumnName, ReplacementTable, NextRow)
in
OutputTable
in
ReplaceAll

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.