Excel First

Excel Solutions

Learning Journey

now browsing by category

 

Combine data files with Power Query

Site White Background

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.

(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

LEN function: Basic and Advanced Examples

Site White Background

LEN function type: Text

Description: Returns the number of characters in a text string
LEN is a basic function widely used, many formulas built for text processing will need this function.

Take a look at the advanced applications examples:

Applications:

Read More

SUBSTITUTE function: Basic and Advanced Examples

Site White Background

SUBSTITUTE function type: Text

Description: Substitutes the old text with new text in a text stringBeyond its basic use, which is fairly simple, the SUBSTITUTE function can be very useful combined with other functions, to get your desired results. Take a look at the advanced applications examples:Applications:
(more…)

Working with Excel Functions and Formulas

Site White Background (more…)

Working With a Defined Name

Site White Background

What is a Defined Name?

 In Excel, you have the option to assign a Name to a cell, or to a Range of cells. This name is reffered to as a Defined Name.A Defined Name will give more flexibility than before, you can refer to it in your formulas, or chart series.Also, the name can be located anywhere in your workbook, you don’t need to use cell or range references.
(more…)

Selecting Excel Table Components

Site White Background
Selecting Excel Table Components is slightly different from selecting normal ranges in a worksheet, because we have 3 Excel Table Components which are special ranges:
(more…)

Order of Precedence

The Order of Precedence used by Excel to calculate formulas is extremely important to know when building complex formulas.

Of course, Excel will respect all mathematical rules you learned in school, there are only a few more Operators in Excel: the Range Operators, single Space Operator, Union Operator, the Concatenation Operator and the Exponentiation Operator.

Read More

Excel Table Components

What is a Defined Table?

An Excel Defined Table is a special object in Excel which adds a lot of new options and functionalities when working with data ranges. With Defined Tables, you can manipulate data more easily than before, adding power and flexibility to your applications.

To create a Defined Table, select the data range, or simply select any cell within the data range, and click the Table button from the Insert tab from Ribbon. The keyboard shortcut key is Ctrl+T to create a Defined Table.

Read More

Excel Reference Operators

There are 3 Reference Operators in Excel:

  1.   “:” (colon) is the Range operator, which produces one reference to all the cells between two cells references, including the cells written in the reference text, like: A11:A13
  2.  “,” (comma) is the Union operator, which combines multiple Ranges into one reference, like  A11:A13 , A11:C11 , A11 (you can refer to simple cells too, not only to ranges with multiple cells)
  3.  ” ” (space) is the Intersection operator, which returns a reference to the cell or to the range of cells found at the intersection of the ranges. In this example, only cell A11 is found in both ranges:  A11:A13  A11:C11

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: