Dashboard in PowerBI: II — Loading a complex CSV file with an R script
In this article, we will load a sales table into Power BI Desktop. Unfortunately, the file that we have been given by the client is not a simple table with one header and one body. Instead, the file contains multiple tables concatenated one below another. At least, these tables follow the same format. But how do we import such a table into PowerBI?
Use R
To make things more complicated, we might expect that the number of subtables may change in the future (e.g. when new work groups are established) and our solution should be robust enough to handle such scenario.
We will not get by here by the mere power of Power BI and we will have to call in higher powers. In such situations, one of the script (Python or R) feature will solve our problem. I prefer using R for working with tabular data, thus I will present a solution integrating an R script in the Power BI Desktop.
Step by step
1. Review the table
First, let us review the file in a spreadsheet and see its format.
The table is composed of multiple tables in the same format. Each of the sub-tables holds sales data for one work group (see the cells B1 and B161).
We want to load this data, cut it into subtables and combine them back into one table with one header and a column specifying the respective work group.
2. Develop the R script outside Power BI
Here comes the point when a bit scripting experience is handy. First, I write a script and test it in RStudio.
The script breaks the table down into multiple tables based on the separation rows (those containing the word “Workgroup” in the first field) and combines the subtables into one table with the rbindlist() function.
3. Copy the R code into Power BI
We need to add New Source → R Script in the Power Query Editor and paste our R code into the window. When we then open the Advanced Editor, we will see our code collapsed into one line:
4. Optional: Change the data file path
As shown in the part I of this Power BI tutorial, we can change the absolute path to our input data into a dynamic one (e.g. when we need to swap our real data with randomly generated). We can do this either in the Advanced Editor or in the Applied Steps (in the pictures below).
Notice the three quotation marks before calling the function PickSourcePath(). Two quotation marks are an escape sequence for a quotation mark in the M language, the third one closes the string.
Summary
In this article, we have seen how to import dynamically a complex CSV file into Power BI Desktop with the use of an R script.
The solution presented in this article can be downloaded from GitHub.
