Learning Journey
now browsing by category
Pattern Matching in Power Query
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 MoreLEN function: Basic and Advanced Examples
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:
- Get the text with the lowest length from a column (first match only)
- Count how many cells within a range of cells have a text length higher than normal
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.
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.
Excel Reference Operators
There are 3 Reference Operators in Excel:
- “:” (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
- “,” (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)
- ” ” (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