Red Gig

In another blog post, I covered ' How to create a Date Table using DAX ' which is one of the most common ways to create a date table in PowerBI. Another method that might be of interest is to create the table using Power Query. Anyone who has used Power Query will know that although it can achieve a similar output to DAX the process is very different. In this post, I'll show you how to create a relatively basic Date Table in Power Query that will contain a list of dates and 5 additional columns with the dates associated with the end of the month, quarter, week, year and fortnight. The process is to create a list of dates and then add custom columns for each of the additional columns that we want to include in the date table Although the most common way of creating a date table appears to be within DAX, I thought this would be an interesting exercise to see what is possible in Power Query (M). Date tables in DAX are expertly covered by the guys over at SQLBI in their article...

I was inspired by a recent article from Greg Jericho ( @GrogsGamut ) in the Guardian so I connected to the source data (abs.gov.au) and created a live dashboard in PowerBI. https://www.theguardian.com/business/commentisfree/2020/aug/30/before-covid-inflicted-carnage-the-australian-economy-was-struggling https://www.theguardian.com/business/grogonomics

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...