Power Query

Home/Power Query

Top tips for Power Query Data Types

by Eric Marshall Assigning Data Types Assigning data types is an essential step when working with Power Query in Excel or Transform Data in Power BI Desktop. Here are some useful tips on how to change data types while maximising the efficiency of your models: Tip #1: Don’t Change Types Until the End of a [...]

Connect to files stored on OneDrive and SharePoint

by Wyn Hopkins How to use Power Query to connect to a file on OneDrive or SharePoint A growing number of organisations are starting to store their files on OneDrive for Business and SharePoint. Users will begin to discover that connecting to these files with Power Query is not as straight forward as clicking the button for [...]

Power Query and a Key Word search

By Wyn Hopkins Power Query and a Key Word search (Episode 1) This demonstration came about due to a real life scenario where I needed to categorise a transaction listing (like a bank statement for example) so that I could generate a summary report of categories of spend. All sorts of tips can [...]

Power Query and a file selector Macro

by Wyn Hopkins Power Query and a file selector Macro   If you have an Excel file containing Power Query and need to send it to colleagues, read on. If they need to be able to change where the source data for the Query is being pulled from then read on. If you [...]

Revealing Power BI’s Hidden Gems

by Wyn Hopkins Did you know about these hidden Power BI features? It seems unfortunate that for such an awesome product there are a few user interface features that are very difficult to discover. I have trained hundreds and hundreds of users in Power BI, and I get to experience people hitting these issues over [...]

2020-07-14T11:07:21+08:00Power BI, Power Query|

HYPERLINK and XLOOKUP to jump to a result

by Wyn Hopkins Use HYPERLINK and XLOOKUP to jump to a result   XLOOKUP will return a range so we can use it inside other formulas such as HYPERLINK to be able to jump to our result. Want to learn more? Jump on to one of our courses We've trained over 1,000 people from all [...]

How to use Icon Sets in Power BI and Excel

By Wyn Hopkins If you find the default Icon settings confusing in both Excel and Power BI, you're not alone! In July 2019 conditional formatting Icon Sets were added to Power BI and it caused us to revisit how they work in Excel. In Excel you would highlight a range of numbers and choose Conditional [...]

Dynamically consolidate multiple ranges

By Yury Tokarev Dynamically consolidate multiple ranges when loading Excel files from Folder in Power Query We've come across a task where we need to consolidate a client's budget data from Excel files, each representing a division, where any of the files may have multiple similarly structured tabs with sub-divisional data. In our [...]

Excel Summit hits Perth

Excel Summit South is hitting Perth The biggest Excel event ever to hit Perth is coming on the 8th & 9th of August 2019. If you love Excel or want to make the most of the technology your organisation is already paying for then you cannot miss out on this. Multiple MVP's and Microsoft staff [...]

We’re going to SQLSaturday 2019

  SQLSaturday training event Access Analytic is once again happy to be involved in SQLSaturday. This event brings together Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics. To find out more visit SQLSaturday here.