Part 2: Combination of rows of tables list in Power Query

Part 1 describes approach showing how to work with List.Generate, Table.FromRecords, List CrossJoin in Get & Transform (aka Power Query, M language).

Homework is done. Advice is digested.

And I’m ready to introduce another solution to combine multiple tables rows.

It is so simple, and can be done in UI!

All regards and honor should go to Imke Feldman (http://www.thebiccountant.com/) as she found that “epic Pokemon”.

I looked at this task as a programmer, thinking about loops and iterations.

Imke – completely differently – as simple user, don’t even thinking about programming, as she said🙂.

Remind, we start from four tables

And want to mix all rows to get all possible combinations (indexed).

How to do this using UI only? Continue reading

Big cake for BI-day

No, it is not another birthday of PowerBI🙂. It is me turned 31 today.

Every year my wife bakes a cake for my colleagues on my birthday.

And every year “birthday, b-day, bi-day, BondarenkoIvan-day” cake for colleagues is decorated by something related to the most interesting moment in my work during last year.

2013 I joined HILTI Russia (HILTI-case), my first experience of work on global scale, a place where I significantly developed myself, started blogging to improve English, met a lot of smart and friendly people, where many things happened with me, long list actually.

2014 we finished six implementations of SAP Business ByDesign, where I was responsible for reporting and workarounds for processes, that system doesn’t handle (cake with flags).

2015 I won HILTI Star Club reward as “best entrepreneur”, optimizing business processes, pro-actively helping everyone with automation of routine, reports, Excel related stuff, simply consulting on best practices, training colleagues and so on (2-floor cake decorated with stars). Deluxe trip to Cancun paid by company was absolutely awesome, unforgettable!

2016 we finally started an official project on PowerBI. Very significant moment.

powerbi cake

If you have some time to explore other sweet works – welcome to my wife’s collection of cakes.

Combination of rows of tables list in Power Query

Recently I faced interesting Power Query problem. Actually, initial problem has nothing related to Power Query.

I required to export quite big volume of data from SAP BW using Business Objects Analysis addin (BO Analysis, or BOA) and save as CSV. Amount of data is literally huge, so it was impossible to export it in one query and even in 10.

I wrote a VBA macro with a loop, where each iteration had to 1) set variables and filters 2) refresh data 3) save result as csv.

So, I had to prepare set of variables and filters for each step of loop, setting filters on several dimensions.

Following four tables describe all possible filters I needed:


Continue reading

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:


Continue reading

Refresh Excel Data Models on schedule

I know what you think.

Yet another solution to refresh Excel files with connections and Data Model (aka PowerPivot).

Plenty of them can be found on the Internet. Different programming languages, different level of complexity

C#:

  1. https://github.com/alapolloni/ExcelRefresh
  2. Excel and PowerPivot refresh suite: https://excelrefresher.codeplex.com/

Perl: http://search.cpan.org/~ctbrown/Win32-Excel-Refresh-0.02/Refresh.pm

Power Shell: https://rpmachado.wordpress.com/2015/07/10/powershell-auto-refresh-excel-powerpivot-data/

VBA: http://velin-georgiev-blog.appspot.com/blog/fully-automated-excel-reporting-by-using-windows-task-scheduler-vba-and-simple-vba-functions-library/

And, of course – Power Update

http://exceleratorbi.com.au/power-update-software/

http://www.powerpivotpro.com/2015/02/introducing-power-update/

Year ago, when I decided to write my own refreshing program, Power Update was already on market, however, I couldn’t get it for myself, due to some reasons. In addition, I wanted to slightly adjust a process of refreshing and saving.

Continue reading

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

Continue reading

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.

Continue reading

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.

Continue reading

Restore Scheduled Tasks

Recently, one of our workstations used for automated reports refresh “died”. OS stopped to load after reboot.

Fortunately, all necessary stuff were copied to backup.

We used “Self-Service BI for poor” approach on that machine. Excel + Vbscripts, scheduled in Windows Task Scheduler.

It’s typical task was to handle following simplified schema

  • Run Excel
  • Open workbook
  • RefreshAll (Power Query connections + Data Model)
  • Save workbook
  • Kill Excel

Repeat for all reports scheduled in Task Manager.

Continue reading

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.

Continue reading