Excel binary files

Since the release of Excel 2007 it has been storing data as an XML file. This is a good practice if we want to exchange spreadsheets between different platforms, or even operating systems, but when it comes to complex calculations, which contain hundreds of thousands of rows, such documents can take up a lot of disk space. This can be overcome by saving with the use of the XSLB extension. You can read about the advantages and disadvantages of this approach in the text that follows.

Multiple criteria MATCH

Sometimes, when working on business reports, there is a need to create a formula that contains several criteria for searching a range. When we have only one criterion, it is enough to use a combination of INDEX and MATCH functions. When there are more of them, it is already a bit more complicated situation. However, this is not too complicated either, you will see in the “recipe” that follows…

Hierarchy of employees

Whether we use Excel BI or Power BI data model, we can create a hierarchy of employees manually or with the help of some DAX functions. In this text you will learn how to use two such functions: PATH and PATHITEM. With their help, it is possible to create calculated columns that show the hierarchical level of each of the employees within the company.

Data profiling tools

When importing tables from data sources there is a possibility of error or import of empty values. Also, in order to optimize the model, there is often a need to see the structure of the data according to the columns. One way to achieve this is to filter the table and view the filter values. It can serve! However, Power Query has built-in tools that allow us to easily view data statistics …

PBIT and PBIDS files

Power BI Desktop reports are stored in PBIX files that contain connections, datasets, transformations, visualizations, and other report elements. If you want to save reports without data, you will use the option of creating a template, by saving as a PBIT file. If you want to save only the link to the data source you will create a PBIDS file.