excel - Excel City

Generation of custom Calendars in Power Query

Ivan Bondarenko October 10, 2015 2 Comments

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 – http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/, 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.

(more…)

Excel online. How it works with dates in text format

Ivan Bondarenko September 8, 2015 No Comments

Using DATEVALUE isn’t a good idea if you publish workbook on SharePoint. You have to be sure that locale at your computer is the same as on SharePoint site, where you publish workbook.

In this example date presented as text. SAP ByDesign always shows dates as text in reports. Text depends on user parameters set in ByD.

It can be DD.MM.YYYY or MM/DD/YYYY, or something else. Unfortunately, from report side we cannot get info about set date format. In our region we agreed that default date format that users should use is DD.MM.YYYY.

Return to DATEVALUE. It is a function that determine date from text string. Reasonable question – what is ‘03.04.2015’ (or ‘04.03.2015’). If you don’t know what date format was used DD.MM.YYYY or MM.DD.YYYY, you cannot answer. But we need answer, therefore Excel uses locale set in “Region and language” parameters.

For example, on your computer date format is DD.MM.YYYY and “28.02.2015” is recognized by DATEVALUE as 28-Feb-2015 – cool.

However, when you upload workbook on SharePoint site where locale set to English (U.S.), DATEVALUE in workbook tries to read date as MM/DD/YYYY, but there is no slash (/) sign in “28.02.2015” – first fail of formula. Even if it would be “28/02/2015”, do we have month “28” in calendar? – second fail.

Instead of using DATEVALUE I would recommend to manually cut string on parts using LEFT, MID, RIGHT functions.

If you are sure that first to characters shows day, month in the middle of string with two characters, year is last 4 characters, then

DATE(RIGHT(A1, 4), MID(A1, 4, 2), LEFT(A1, 2)) – will give you right date. This option works without dependency on locale of computer or SharePoint site.

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

Ivan Bondarenko April 17, 2015 8 Comments

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

Ivan Bondarenko April 16, 2015 9 Comments

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

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 vol.2

Ivan Bondarenko April 16, 2015 No Comments

In previous post I described list of ways of data extraction from SAP ByD. Here I continue story about case “ByD to Excel” using SAP ByD Excel add-in.

Currently, I use version 135.0.2109.722 of SAP ByD addin for Excel

In name mentioned that add-in for Excel 2010, but I’m using it for a long time with Excel 2013. From time to time have same problems that I had when used Excel 2010.

This is a basic way that SAP offers to SAP ByD consumers. However, when I opened for myself Power Query and Odata protocol in ByD, I use ByD Excel add-in only for temporary solutions or quick data exploration.

Assume, that you inserted report on worksheet. Say, Invoice Volume.
(more…)

ABC-analysis, method of Triangle in Excel

Ivan Bondarenko February 25, 2015 No Comments

This post continues topic described in two previous posts – ABC analysis. Very popular, simple method, however in most of cases non-accurate.
In previous post I showed how to increase accuracy of ABC-analysis by using special method – Triangle method. And now time to show real example.

Assume that we have list of sales

To apply method of triangle we have to
1. Sort data descending by Net Value

2. Convert Net Value to Net Sales – exclude possible Returns.
=IF([@[Net Value]]<0, 0, [@[Net Value]])

3. For each row calculate part of total Net Value – NS Normalization: [@[Net Sales]] / SUM([Net Sales])

4. For each row calculate [Contribution in result]: sum of [part of Net Value] from 1st ranked position till current row =SUM(SALES[[#Headers],[NS Normalization]]:[@[NS Normalization]])

5. Make normalization of rank: =[@Position] / MAX([Position])

6. Find Pareto Point – point where [@[Contribution in result]] + [@[Position Normalization]] = 100% (or closest point).

Resulting table

Then we will be able to find Xp and Yp – coordinates of Pareto Point.
In example file I placed these coordinates in separate cells and gave corresponding names to that ranges: “Xp” and “Yp”.

After that, using formulas

Which in Excel are:
=(Xp*Yp – Xp^2) / (2*Yp)
=(3*Xp*Yp + Xp^2) / (2*Yp)

We can find upper bound of groups A and B. And then mark all positions with corresponding class.

Such calculation can be easily done in PowerPivot where we can avoid sorting and grouping of transactional sales data – DAX can handle all necessary calculations.

Example workbook can be found here.