Red Gig

In a recent blog post, I described the steps required to connect PowerQuery to a folder on a local drive using the Folder.Files function in M. The Sharepoint.Files function uses the same process to connect to folders on Sharepoint. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder on sharepoint.  Once you have something similar the process you need to carry out is as follows: Open PowerBI or Excel, both of which contain Power Query. I'll be using PowerBI for this demo. Click on the button labelled 'Get Data', then 'more' and then when the following window appears, select 'Sharepoint Folder' and then click 'Connect' Then in the next window, enter the site URL (not the folder URL) which contains the files you'd like to combine then click 'ok in the window shown below The following window will display all of the files that are on the Sharepoint site with some buttons a...

When working with text in Excel its often useful to combine the text from two or more cells into a single cell. This can be done with a couple of different methods Method 1 - using the Concatenate function I've previously blogged about the basics of this function in this post . Here we'll look at the function in a bit more detail and some common use cases. Almost all people I've helped with Excel aren't familiar with the word 'Concatenate', its definition is "link (things) together in a chain or series." which is exactly what the concatenate function does. Its simplest use case would be, = Concatenate (A1, A2) This would combine the values in A1 and A2 into a single value which would be displayed in the cell in which the function is placed, lets look at a real example Here we have a list of first names and last names and we'd like to combine them into another column titled 'Full Name'. We'll begin by using the Concatenate function and ...

One of the most useful functions in Power BI is the ability to connect to data on the internet. One of the benefits of using this method is that any changes to the information on the internet can be updated in the data model using the 'refresh' function.  When you do this for the first time you'll be amazed how easy it is. In this post, I'll show you how to connect to some information on Wikipedia. The process to do this is as follows: 1. With Power BI open, click on 'get data' then 'web' 2. When the next window opens you'll need to paste the URL of the page that contains the information you would like to import into your data model. In this example, I'm using the page  https://en.wikipedia.org/wiki/List_of_colors:_A%E2%80%93F 3. Once this is pasted, Power BI will need to understand if any credentials are required to access the web page, in this instance, there are no credentials required so we'll need to ensure 'Anonymous' is selecte...

So you've added some data to your data model using the 'enter data' button and you've realised you need to add another row, column or possibly remove some data. It might not be immediately apparent how to do this and I suspect some would delete the table and re-enter the correct information. Theres no need to do this as there is an easy way to modify the original data entered. Here's how to do it. 1. Right click on the table you wish to enter and select 'edit query' 2. In the PowerQuery Editor, double click on the first step labelled 'Source' in the Applied Steps section 3. Now you'll see the 'Create Table' window where you can edit the data you originally entered and then click 'ok' when you're done. Thats it!

If you've created a measure in Power BI and accidentally put it on the wrong table, no need to panic! moving a measure from one table is very quick and easy once you know how to do it. To move a measure from one table to another, all you need to do are the following steps 1. Switch to 'Model' view (or where you view the relationships in your model) by clicking on the bottom icon of the 3 on the left hand side of the window 2. Make sure the table containing the measure is expanded in the 'fields' list 3. Locate the measure and then drag it from its location to the table in which you would like it to be stored Thats it!

If you've learnt Power BI organically and developed your skills using 'real world' applications you've probably learnt a couple of things the hard way. One thing I realised on my journey with PowerBI was that its very easy to create many measures and then end up with a model that’s difficult to manage and maintain. Initially, I would create measures and place them in the table that felt like the best place to store them, this worked for a while but inevitably didn’t quite work. I also tried putting a 'special' character at the start of the name, in my case a # symbol, to easily differentiate a measure from a column, this also didn’t quite work. After doing some research, I found the best solution to this issue was to create a Measures table and put all of my measures in this table. In this post, I'll show you how I created the measures table.   There are a couple of methods to create a measures table, the most obvious one is to create a 'table' u...

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...

When presented with a table of data that requires analysis, one common requirement is to calculate a running total or what some might call a cumulative sum of individual values. One application might be a table containing the amount of money spent across a number of transactions and there is a need to provide a running total to show the total spend across the data set. There are a couple of different methods to achieve this but the easiest way is to use the function =sum($B$2:B2) where column B contains the values that you wish to add in a running total. This gif (below) is the above function being added to a table containing values. I've used the keyboard shortcut F4 to set the cell reference to absolute so that when it fills down, the second part of the cell range increments with each row.

I recently blogged some instructions detailing the process to create a Date table using DAX in Power BI which is one way to create the foundation that enables the time intelligence functionality of PowerBI. If any of you have tried to use a date table you'll probably be aware of a couple of 'nuances' that can result in an incorrect result when you begin to visualise the data. I've described the 4 issues I've often had to rectify when working with time series data, hopefully they'll help you with similar issues I've incurred and found solutions to. Lets assume that you've created the date table and imported the sales data (more detailed explanation and link to files below). You then try and make a bar chart showing the number of sales by date and you end up with the following: I think most people who've used PowerBI have seen something similar, all of the columns are the same height and are of a magnitude that immediately feels incorrect. If you see t...