Red Gig

Most of the time data will need to be imported into the data model from a source so that the connection is 'live' and can be updated by refreshing the source data. It is also possible to enter data straight into the data model through the 'Enter Data' function. This function allows data to be typed or cut & pasted into a table. This way of entering data can be used to hard code any lookup tables that might currently be Excel files. If the data is hard coded in the model using the process below then the risk of links to files and filenames is mitigated. Here are the steps required to enter data directly into PowerQuery In the ribbon under 'Home' click the 'Enter Data' button to display the 'Create Table' window Enter the data either by typing it in or cut and paste from another source. Be mindful of the header row to make sure if you have headers, the headers are pasted into the header row. Columns can be inserted or deleted by right clicking ...

A recent discussion on R/Excel  " What is the most efficient way to insert a similar chart in several pre-existing worksheets of a workbook while making sure that the data source of the chart refers to the worksheet where it has been inserted?"  initiated a conversation about the different methods of displaying data from multiple sheets which i ncluded a question around working with data on multiple sheets that needed to be summarised on a chart that was identical on each sheet. As with most things with Excel, there are a number of methods that would achieve this result, I believe the quickest and most efficient is to use Power Query to combine the data and then display the output as a pivot table/chart with a slicer to allow the user to select each source as required. I realise this isn't providing a separate sheet for each data source however, assuming there are no restrictions on the visibility of the data, this is a 1-page solution that contains all the required data ...

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