Unfold Child-Parent hierarchy in Power Query

Ivan Bondarenko September 9, 2016 3 Comments

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…)

Refresh Excel Data Models on schedule

Ivan Bondarenko August 30, 2016 3 Comments

UPD: Excel-based version of Power Refresh (Reports Controller) is here.

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.

(more…)

Query folding and dynamic parameters in SQL Queries in Power Query

Ivan Bondarenko July 17, 2016 2 Comments

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

Ivan Bondarenko June 16, 2016 8 Comments

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

Ivan Bondarenko June 1, 2016 9 Comments

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…)

Restore Scheduled Tasks

Ivan Bondarenko May 17, 2016 No Comments

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.

(more…)