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.


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.


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.


Several ways of data extraction from SAP Business ByDesign

There are several reasons to extract data from corporate ERP system.

First one and the most important – reporting. Nicely visualized data, usually pre-calculated using specific logic before. We all want to see beautiful reports.

Second, I think, is a data transfer to: BI system(s), DWH etc. Maybe for some companies this is a first priority instead of reporting.

Third, rare, but happens in my practice – data migration due to system “end-of-life”. Yes, this happens. What to say? In a moment I’m involved in such project, second in my practice.

I would like to share my experience. Hope it will simplify life of many other reporting specialists working with SAP Business ByDesign.
First of all, basic ways of getting data:

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


Excel online. How it works with dates in text format

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.

Power BI is generally available now

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?

Reporting and modeling for SAP Business ByDesign vol.2

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
ByD Addin Version

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.