If you are regularly using Power Query to create the same transformations on the same dataset, it may be preferable to create a template file so that you don’t have to always start from scratch. In the Power Query window, go to Advanced Editor from the Home tab. Copy the full code from the ‘let’ Read More …
Category: Power BI
Convert Power Pivot data into an Excel table
If you have a Power Pivot data model but want to make some of the information accessible in a row by row listing, this can be done by following these steps: From the Data menu, within ‘Get External Data’, select ‘Existing Connections’ and go to the Tables tab. Select your import data option, it can be Read More …
Power Pivot – Drill down to more than the first 1,000 rows
When you double-click a value on a Power Pivot table to view a listing of the results, by default the pivot table will only return the first 1,000 rows. To expand this limit, go to the Data tab and click on Connections. In the dialogue box, click on ‘ThisWorkbookDataModel’ and go to Properties. In Read More …
Merging multiple files with Power Query
A common problem in businesses is having several versions of the same spreadsheet being used by multiple different people making it difficult to combine them for any centralised reporting. It can be done using VB, but not particularly easily, but then Power Query came along and made this a quite simple step-by-step process. There are Read More …
Power Query (Get & Transform) overview
This overview aims to provide a basic understanding for Excel users who haven’t yet got to grips with Power Query, to get you up and running with it. If you are already accustomed to doing whizzy things in Excel with macros and VBA, or you have experience with Graphical User Interface (GUI) query tools like MS Read More …
Power BI add-ins
What is BI? BI, or ‘Business Intelligence’, is the generic term for the process of transforming data into meaningful information to increase understanding and awareness around a business and help management to make informed decisions. In recent years corporate organisations have really started to see the value in developing their Business Intelligence. Whether the Read More …
Unpivot data using Excel Power Query tools
Here is an example of how you can use Power Query tools to ‘unpivot’ columns of data to make them easier to report from. This is the kind of thing that would have been quite time-consuming to do in Excel before the ‘Power BI’ era, either by manually repeating steps or having to spend time writing and Read More …