Consolidate files

The goal of this example is to produce an integrated table based on three text files stored in separate network paths:

1. Create a query to one of multiple text files that need to be integrated.
2. Click on Get Data and choose Text/CSV from the list of file sources.
3. Navigate to the folder and select the file to query.

• In this example, the three text files are tab delimited but the same process applies to other delimiters and file sources.

• If the files are stored in a single folder, a combine binaries transformation could be used. See the There's more... section for additional details.

1. Apply Transformations to Prepare for the Consolidation.
   • Promote the header row and set the data types.

• Use Table.PromoteHeaders() and Table.TransformColumnTypes() to prepare the table for integration with the other two files.

• When connecting to an individual file such as this scenario, using the built-in data connectivity options via the interface is more convenient.

1. Give the query a name such as Sales-2015 and duplicate the query.
2. In each duplicated query, modify the file source connection to reference the given file directory path.
3. Disable the load for these queries and add them to a query group.

1. Create a new, blank query for integrating the text file queries.
2. Use the Table.Combine() function to return a single table based on the rows of the three other queries.

• No other expressions are necessary in this example given the transformations applied against the source queries in step 2.
• Depending on the scenario, the developer could apply Table.Distinct() functions to avoid any duplicate rows from reaching the data model.
• Selecting the Sales table in Query Dependencies View highlights all input queries and their source files.
• Hovering over the data sources provides additional details (network path, server, and database).