Red Gig

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...

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

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