Learning to Fly
PowerQuery is a very powerful tool. It allows us to reach many different data sources and transform the data into the format that is most useful to us.
And all this through a really friendly and easy-to-use user interface.
But sometimes this interface is not enough and to get the results we need, we have to go further and write some M-code.
The simple idea of writing code scares most Power BI users, especially business users. But writing M doesn’t have to mean writing hundreds of lines of code: most of the time we just look at the code written for us by the PowerQuery user interface and then adapt it to our needs.
Often you don’t even need to write code, you just need to be able to read it, understand how it works and adapt it to your needs.
Recently I came across a situation like this, when during a dashboard in a day, a participant asked me how to solve a problem that was insurmountable for him: to load in a single Power BI table the sales data present in a database but distributed on n tables, one for each month.
The solution is much simpler than you think.
The scenario proposed by the participant is very similar to that of having to load many identical files in a folder. For this last scenario in Power Bi there is a special connector.
So why not take a look at the M code written for us by the Folder connector and try to adapt it to our situation?
The folder connector loads all the files inside a folder by splitting the problem into two simpler parts.
On one side a query that loads a single file. Transform Sample File, in the image above.
On the other a query that loads the list of files in the folder.
Basically then PowerQuery does nothing but transform the first query (in which the file name is not hardcoded but managed by a parameter) into a generic query, that is a function, that accepts as parameter a generic file.
Then it uses this function calling it inside a new column of the query that extracts the list of files.
In each row of this new column we will have a table with the data contained in the file of the same row. It will be enough then to explode the content of this column to have the details of all files already inserted in a single table.
Returning to our original problem, we will then simply build a query that reads any of the tables that contain the monthly data to be loaded and then transform it into a function that accepts the table name as a parameter.
We then need a second query that loads the list of tables we are interested in. To do this, in case our source is a SQL database, we can query one of the system views sys.tables, filter the data to keep only the list of tables that interest us.
Then we can add a column that calls the function created before passing it as a parameter the table name.
We explode the contents of the new column and that’s it.
This technique can be replicated to many different cases, not automatically managed by the PowerQuery user interface, such as an Excel sheet with many sheets (for example one per month) that contain data to be merged into a single table. And we can also imagine having several such excel files, one per year. As complex as this scenario may seem, we can now solve it quite easily. Aren’t we?
