TEXTJOIN and Array IF Statements

Looking up and Combining text using TEXTJOIN and Array IF Statements.

Looking at the table of data below, how would you go about looking up the order number and pulling the name of all products sold on that order into the one cell as shown in cell B19 in the image below? Looks complex right?  But not really with the use of TEXTJOIN and Array IF […]

No More Complex Nested IF Statement with the use of IFS

Nested IF statements are the thorn is the side of many Excel users.  They can become long complex formula that are difficult to both read and write. However, if you have been using Excel 365 one of the new functions available is IFS.  Now this function is also available in Excel 2019 Standalone as a […]

Power BI – Connecting to tables on a website and transforming that data

CLICK HERE TO LEARN HOW TO DO THIS IN EXCEL WITH POWER QUERY Connecting to data sources and transforming data into a useable format can be complex, even when you are using a powerful tool like Power BI. I know many people that have started a transformation only to give up because they are not too sure […]

Analyzing a Blockchain Database with Power BI – Introducing SteemitSQL

Sometimes it can be difficult to find a good data set to analyse.  Kaggle offer data sets and run competitions on analysis and Microsoft also offer sample data sets such as World Wide importers.  But sometimes a completely different data set is really cool to explore. Over the last few weeks I have been using […]

Easy Excel Lessons – Transposing Data and Converting Text to Columns

Transposing Data This worksheet shows sales for each salesperson by month: Let’s say that you want to have the months appear as columns, while the sales associates appear as rows. Making this change manually would be a very time consuming process, but luckily Excel 2013 has a way to swap this data for you. To […]

DAX – FILTER inside CALCULATE

CALCULATE in DAX is such a powerful and complex function to fully understand.  In essence what CALCULATE will do is modify the current filter context.  And it does this by evaluating both the current filter context and the filters applied by CALCULATE. The syntax for CALCULATE is: =CALCULATE ([Expression], filter1, filter2…..) So if CALCULATE contains […]

Learn how to Group Data in Excel and use Subtotals

Grouping Data Last week we looked at the Outline feature in Excel. While the automatic outlining capabilities in Excel 2013 work very well with numerical data, it is not so effective when working with non-numerical values or data that has no distinctive totals. Luckily, you have the option to manually group data in Excel. In […]

Context Transition in DAX for PowerPivot, Power BI & SSAS

Context Transition in DAX is when you transform the row context into the filter context.  In order to understand Context Transition a practical knowledge of Row and Filter context is needed, along with a practical knowledge of CALCULATE. Understanding Context Transition is not too complicated, however it is something that DAX users get caught out […]

Its rather simple to use the Outline feature in Excel

The Outline Feature in Excel Excel’s automatic outlining feature makes outlining a worksheet easy. Automatic outlining works best with numerical data organized into groups and sub groups by formulas or functions. For example, the sample image below contains monthly sales data, organized into quarterly and yearly totals using formulas: As you can see, it can […]