Very often we come to situation when we need to somehow transform list elements or, what is more often, table column.
And often standard transform functions do not provide a solution.
Assume we have following table
Typically, what we would like to do in such case – remove numbers.
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.
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.
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.
From the first glance Power BI is a dream as self-service BI if only cloud publishing platform is allowed in organization.
Set of short videos shows how easy is to work with Power BI
Power BI Desktop GA Update Overview – overview of new features of Power BI Desktop application.
How to create and use the Personal Gateway to keep your data up to date. – connect reports in cloud with on-prem data through own workstation.
How to create content packs for your organization to use in the Power BI service. – packs of reports, create, share with groups of colleagues.
How to connect a file from OneDrive to Power BI. – very useful scenario, when workbook with data stored on Onedrive.
I consider important to try “packs of reports” feature. It is easy when reports are done for one small company, where reports divided on two or three groups; but will it be easy to maintain such structure on Power BI for 10 countries with 5 areas of reports in each, where every group should be isolated?
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
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.
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…)
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…)
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.