Excel First

Excel Solutions

Combine data files with Power Query

Sample-automatic-File-Combiner-in-Power-Query

Data variety is a challenge when you need to combine it into a single table. If data is consistent over time, the usual tools within power Query will not fail, but this scenario rarely happens in the real life.

Most common issues when combining data:

  • there is a change in headers, refreshing the query fails to identify new headers;
  • data comes in a different format than expected: xlsx, csv.

Download the demo files to try the solution:

Make sure you unzip the files on a location from your computer, then update the path from Settings sheet (in the file named GetCSV and XLSX from folder.xlsx) to point to your Data folder new location.

Why Power Query fails to refresh when there is a change in source data?

When you use the default tool “Combine Files” when you Get Data from Folder with Power Query, the application will analyze the first data file from that folder, or you can select the file that will serve as reference.

Read More

Pattern Matching in Power Query

White image Background

In Power Query there is no tool yet for matching regular expressions (patterns).
Only in Power BI we can run scripts in R and Python, hopefully these languages will be added to Excel Power Query.

Until then, to solve this problem, with a little imagination, we can design our own pattern matching process.

Here is a file you can download and test:

Read More

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: