Power Query is the tool Excel and Power BI use to acquire and transform data from a vast array of data sources. I’ve come to appreciate Power Query as a tool for importing data into Excel. The final destination of this data may be Excel (as a refreshable report) or Dynamics GP (through the SmartConnect Excel Add-in and web service).
A common use of the SmartConnect Excel Add-in and web service is integrating data that has been aggregated in Excel into Microsoft Dynamics GP. Even in this cloudy and connected world, this data frequently originates as plain and simple text files.
Power Query’s folder data source is ideal for a scenario where all the files in the imported folder share a common structure and format (for example, column-based text files). So, what happens if the folder suddenly contains a mix of text and Excel files?
With a few workarounds, the folder data source is still a very viable option for the scenario of mixed file types.
How to Use Power Query’s Folder Data Source with Multiple File Types
Select Power Query’s folder data source and the appropriate folder.
The following table is presented in the Power BI query editor. If the folder contained only text files, the Combine button on the Content column would merge the individual files into a single table. Unfortunately, the presence of the Excel requires some additional steps.
A custom column can be created to hold a table containing the data derived from each file format. Through this approach, a different set of rules can be defined for extracting the contents of each file format. While this example is limited to text and Excel files, it is possible that there are several file formats, each requiring its own set of rules.
Select Add Custom Column. Enter a name for the column. In this case, Data.
A bit of a warning before the next section. This is going to require a few lines of M (Power Query formula language) code. For those not familiar with M, many useful examples can be found on Microsoft’s MSDN site and elsewhere.
The custom column formula, via the If / Else construct, allow the correct rules to be applied to data for the individual file formats.
In the case of the text (CSV) files, the Csv.Document() function is used. In the case of Excel files, the Excel.Workbook() function is used. As a quick tip, M is case sensitive.
The custom column now contains a link to the table containing each file’s data. The column can be expanded by clicking the Expand button. Each file’s data is now combined in a single table. A quick review of the table reveals that the column headings from the files have been included as data.
Select Use First Row As Headers to convert the first row of data into column headings. Rename the columns to relevant names.
There is an unseen problem with this data. The column names from the other files are still part of the data. Apply a text filter to the TranDate column to remove the remaining column headings.
As powerful as Power Query is, there are instances in which it inappropriately selects the column’s data type. This appears to be the case as it has assigned the Any data type to the four columns of the files. This data type may be prone to errors and should be replaced by the appropriate type.
For the Transaction Date column, select the Date data type. For the Account and Department columns, select the Whole Number data type. Finally, for the Amount column, select the Currency data type.
That’s it. You can now import the query into Excel (as a table) using the Close and Load option. While this may have appeared an arduous process, remember that the query is now saved and can be repeated when the data is updated.