Power BI MCSA Certification Tips – Part 2 : Connecting to Folders

To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.

We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.

In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.

The topic of connecting to folders is common to both the 70-778 and 70-779 exams.

If you have a series of files which all have the same format and are, perhaps, supplied by different individuals or dumped periodically from another system; one useful strategy that you can employ is simply to place all the files in the same folder, and then to use Get Data > Folder to combine them into a single query/table.

Connecting to a folder of data is a very powerful feature; the files being combined can even be placed inside sub-folders. However, to produce useful results, all the files must have common column headings, though not necessarily in the same order.

To connect to a folder, in Power BI, click Home > Get Data > More > Folder; in Excel, Data > Get Data > From File > From Folder. The screenshot below shows the Power BI experience.

The preview window displays a table of metadata about the folder to which you are connecting.

·        (A) Content column
The content column is the most important of the metadata columns since it contains the binary information which will be combined to produce the query.

·        (B) The Combine buttons
The Combine & Edit and Combine & Load buttons are the ones which will produce a query consisting of all the rows of data in the all files in the folder; and will take the user to the Query Editor and Power BI Desktop, respectively.

·        (C) The Load and Edit buttons
Clicking Load or Edit will not produce a query containing combined data; but, rather, will load or edit the metadata. This option is useful if you wish to apply a custom function to the folder, rather than relying on the automatically generated workflow.

Clicking either of the Combine options will display the Combine Files dialog.

·        (A) Example File
The example file will be used as the template which will determine which columns will be combined to produce the final query. This option provides a useful mechanism for coping with inconsistencies within the files.

For example, imagine a scenario where some files contain the headings “A”, “B”, “C”, “F” and “G”; while others have the headings “A”, “C”, “E”, “F” and “H”. If your example file contains the headings “A”, “C” and “F”, only these columns will be included in combining the files; and columns “B”, “E”, and “G” will simply be ignored.

·        (B) Combine Files options
The options displayed in the Combine Files dialog will depend on the types of file being combined. Power BI will assume that all files have the same structure as the file nominated for the role of example file.

When you choose an example file and click the OK button, Power BI creates a number of queries in order to produce the combined file.

M Language Syntax

Folder.Files

The M language function behind Get Data > Folder is Folder.Files (), which returns a table containing a row for each file found in the given folder and any subfolders. Each row contains metadata about the folder contents and a binary link to the contents of the file.

Folder.Files(

path as text

) as table

The path argument is simply a text file which is the literal path to a folder.