Unfold Child-Parent hierarchy in Power Query

In this post I consider two types of hierarchies


First table defines typical hierarchy of companies.

Usually, such hierarchy is used for financial reports to group key figures.

Most probably, we know number of hierarchy levels upfront, but not always.

Task: Expand hierarchy, automatically detect quantity of levels in resulting table using Power Query (Get & Transform) / Power BI

Stay close to scenario described in pattern: http://www.daxpatterns.com/parent-child-hierarchies/

Goal:


Second table defines substitution of products.

Case: sometimes we required to get sales history of all predecessor products and group result on Actual Product. There can be multiple substitutions, we never know amount.

Task: In this case we need to find the latest successor, Actual Product for each product in other words.

Goal:


(more…)

Query folding and dynamic parameters in SQL Queries in Power Query

Before you start reading this article, I recommend you to go through very good post on Ken Puls’s blog – Pass Parameters to SQL Queries.

If it is fine for you to use native queries to database then most probably scenario described below is not so interesting for you.

Here I’ll talk about workaround allowing to use query folding and pass parameters in Power Query without building of native SQL query.

Level: intermediate

Task definition

Generate N workbooks with data models populated with limited data scope.

Then each workbook will be shared with certain group of people, that allowed to see only relevant set of data (e.g. department OPEX of particular subsidiary).

This is dictated by fact, that if you share Excel workbook with someone – user immediately has access to all info inside data model. So, I cannot create one common data model and hide slicer somewhere with activated department. I must prepare separated workbooks.

Conditions

Data: Table with millions of records in SQL database – main data source for report (query folding required for obvious reasons)

Tools: Excel + Power Query + PowerPivot

Support data: table in Excel, defining datasets – data scopes, let’s call it Control Table

(more…)

Bulk extract data from SAP Business ByDesign

Post describes how Power Query (Get & Transform) can be used as ETL tool for SAP Business ByDesign (SAP ByD).

Purely Self-Service BI solution built on Excel for SAP Business ByDesign (SAP ByD).

Level: Advanced

How to extract master and operational data from SAP ByD using Power Query

Common information can be found in my previous posts, which I’d recommend to go through

Several ways of data extraction from SAP Business ByDesign

Query SAP Business ByDesign Web Services via Power Query

From all possible methods of data extraction, I chose Excel + Power Query as the most simple one for Modern Excel data analysts (non-IT pro).

What do we need to start this journey?

What can help?

Massive data extraction from SAP ByDesign

Let’s start from the most interesting – extract of large data volume from SAP ByD.

(more…)

Power Query: Xlsb vs Xlsx

Recently I helped my colleague with a report. The task was to consolidate data from multiple Excel files, each contains 100k-500k rows. Hence, size of each Excel file is relatively big.

I proposed to use xlsb to store those files. One file per month, report needs R24m, so 50 MB is less than 150 MB.

In the end I finished with deep analysis what is better as Excel-files database for Power Query – XLSB or XLSX.

Resume

For PQ solutions use XLSX instead of XLSB

PQ from XLSX twice faster than from XLSB

Engine handling XLSB from time to time return error – cannot read file

“UseHeaders” parameter of Excel.Workbook function works differently for xlsx and xlsb

If sheet and table names are equal in Excel workbook – Navigator generates unique name of object.

If you interested how I revealed all this – continue reading.

(more…)

SAP ByDesign + Power BI = cloud friends


In this post you will find an idea how to pull data from SAP ByDesign directly to Power BI. This is a totally self-service solution, which can be built if you have literally nothing. Only you, Excel, free license of Power BI and SAP ByDesign tenant(s).

Samples based on data from test tenants that were opened for participants of openSAP course “Reporting with SAP Business ByDesign“.

Couple of words about SAP ByD for those who are not aware what is this. Cloud ERP system, has own real-time analytics engine, quite powerful, but poor. Poor calculation capabilities, poor visualization capabilities. Handles only basic things, obvious calculations.

Moreover, if you have several SAP ByDesign systems in responsibility you most probably have to consolidate data somewhere. Build sub-regional, regional reports etc.

(more…)

Rename columns of nested tables in Power Query

Typical scenario

You have several data sources – Table1, Table2 and Table3

You want to combine data


Unfortunately, column names are different in those data sources, but structure is similar.

One of possible solutions is to create three separate queries, apply necessary transformations there and combine them after all.

And this would be fine solution. Moreover, it is only possible solution when function of data extraction is different for each data source.

Scalability: when we get another source, e.g. Table4, we will create fourth query and add it in Table.Combine() in addition to previous three.

But! We can slightly simplify scalability process when same extraction function used for each data source.

(more…)

Query SAP Business ByDesign Web Services via Power Query

In this post I would like to share my experience about work with web services using Power Query.

Unfortunately, some very important information is not available in data sources, e.g. Accounts Relationships and Pricing Conditions (price lists, customer specific discount lists etc.). Hope someday we will get this in reports.

However, currently, we can get this info only from

  • UI manually, almost impossible to do if you have X tenants and XXX customers with specific price lists
  • Web services – Jedi path, require knowledge and skills, or trustable tools, that can be checked

To our luck, with every new release we get more and more web services.

Basic idea of web service usage

  1. Make POST http request with XML
  2. Parse XML response

(more…)

Load Power Query function from GitHub

Why scenario of “Loading Power Query function from GitHub” is interesting?

Assume that you work in big company, or small, doesn’t matter.

You and your colleagues use shared library of Power Query functions, published on shared drive.

You also have separated workstation or server, that has no access to the internet, only can access shared folders in corporate network. This workstation updating workbooks with Power Query all day and night long.

Typical model
101615_0737_LoadPowerQu1.png


From time to time, you also have to collect data from web using PQ functions. E.g. if you are using cloud ERP, like SAP Business ByDesign.

When you are in the office, everything is OK, your laptop is in corporate network, you simply take functions from shared folder.

Of course, company’s network resources are not accessible from the outside – from home, from partner’s office or other place.

You probably can use VPN, it solves problem, however, partially. Because VPN reduces speed to some sites.

What would be good in such situation

  • library on the web that can be used by you and colleagues from everywhere
  • library on corporate network for isolated server

If you have such two libraries then following scenario becomes possible :

When you or your colleague in the office – you simply take function from corporate network

When you outside of company network, you load same function from web library

This simple function, Load.m published to GitHub, will help you

https://github.com/IvanBond/pquery/blob/master/Load.m

(fnName as text, optional BasePath as text) as function =>
let
//If you wish to hardcode the path to load the queries from, you can edit the following line:
DefaultPath = “C:\PQuery\”,
GitHubPath = “https://raw.githubusercontent.com/IvanBond/pquery/master/“,

BasePath = if (BasePath <> null) then BasePath else DefaultPath,
Path = BasePath & (if Text.End(BasePath, 1) <> “\” then “\” else “”),
File = Path & fnName & “.m”,

Function = try Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(File))), #shared)
otherwise Expression.Evaluate( Text.FromBinary(Binary.Buffer(Web.Contents(GitHubPath & fnName & “.m”))), #shared)
in
Function

If you provide BasePath as parameter – function will take code from there.

If BasePath is empty then Load.m will use hardcoded DefaultPath.

If Load.m failed to load function from BasePath and DefaultPath – then it goes to hardcoded GitHubPath.

GitHub library opens wide opportunities, because it is shared, open-source, accessible from any place.

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.comhttp://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…)