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 […]

Related and RelatedTable – DAX for Power Pivot, Power BI and SSAS

When we looked at the SUM and SUMX expressions in an earlier article and video we introduced the use of RELATED. You will remember this expression; =[Qty] * RELATED(products[Sales Price]) Where we took the qty sold from the sales table and we went across to the related products table and found the relevant sales price […]

Resolve Errors in Excel Formula

How to Resolve Errors in Excel Formula

It is not uncommon to encounter errors in spreadsheets.  But thankfully Microsoft has equipped Excel with tools to help resolve errors in Excel formula.  This include the ability to evaluate formula and set error checking options. In this article we will cover step by step the options available to help resolve errors in Excel formula. […]

Step by Step – Named Ranges in Excel

Before we really get stuck into this article please go ahead and download the workbooks so that you can practice along step by step. There is no email or sign up to download the files.  This training is free for all and the best way to learn Excel is to practice along. Wanna learn more […]