Skip to content
Excel Off The Grid Logo
  • Home
  • Blog
  • Courses
  • Books
  • Consultancy
  • Member Login

Category: Power Query

Power Query - Connect to OneDrive Personal files

Power Query – Connect to OneDrive Personal files

Posted on May 5, 2022 by Excel Off The Grid

I shared how to get data from OneDrive or SharePoint into Power Query in a previous post. Many people noted that the method shown in that post works with OneDrive for Business but doesn’t work with OneDrive Personal. So, in this post, I will demonstrate the technique needed to connect Power Query to files in Read More

Posted in Blog, Power Query Leave a comment
Power Query - Connect to OneDrive or SharePoint

Power Query – Connect to OneDrive or SharePoint

Posted on April 21, 2022 by Excel Off The Grid

In Power Query, when we connect to a local file, it’s reasonably obvious how we do that; it’s built directly into the UI. We just need to navigate to the location… Bam. Done! However, what if we want Power Query to connect to a file on SharePoint or OneDrive? How do we do that? It’s Read More

Posted in Blog, Power Query 3 Comments
GETPIVOTDATA vs CUBE functions

GETPIVOTDATA vs CUBE functions

Posted on March 24, 2022March 24, 2022 by Excel Off The Grid

We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas. As great as PivotTables are for analysis, they are not always the best for presentation, which is why extracting data using a formula is so useful. We currently have Read More

Posted in Blog, Formulas, Power Query 4 Comments
Power Query: Stop Excel Table columns resizing

Power Query: Stop Excel Table columns resizing

Posted on February 24, 2022 by Excel Off The Grid

If we use Power Query to load data into an Excel Table, we may encounter an annoying default setting, whereby Table columns resize every time we refresh the query. It isn’t a problem if this is just data used for other calculations. However, if the query is part of your reporting, it may not display Read More

Posted in Blog, Power Query Leave a comment
Filter All in Power Query

Filter All in Power Query

Posted on February 17, 2022February 17, 2022 by Excel Off The Grid

Filtering in Power Query reduces the table to include only those items which meet specific criteria. However, Power Query doesn’t have an option to filter to include all items. In Excel, we can use the asterisk ( * ) as a wildcard character to match any text. Therefore, if we want to include all items Read More

Posted in Blog, Power Query 3 Comments
Get the refresh date & time in Power Query

Get the refresh date & time in Power Query

Posted on February 10, 2022February 15, 2022 by Excel Off The Grid

Knowing when our data was last refreshed is essential. It lets us know whether everything is up-to-date. So, in this post, I’ll show you how to get the last refresh date and time using Power Query. This post is in response to a Power Query question I received from Christian: “I use a CSV file Read More

Posted in Blog, Power Query 4 Comments
Stop the auto Changed Type step in Power Query

Stop the auto Changed Type step in Power Query

Posted on January 27, 2022 by Excel Off The Grid

Power Query in Excel and Power BI has an annoying default feature that automatically applies the Changed Type step even if we don’t want it. It generally occurs when creating new queries from non-database sources, but it also appears after some basic transformations. In this post, I want to show you how to stop the Read More

Posted in Blog, Power Query 2 Comments
Rename columns in Power Query when names change

Rename columns in Power Query when names change

Posted on January 13, 2022 by Excel Off The Grid

Power Query is great for extracting data from a source, reshaping it, then loading it into Excel. A standard part of the transformation process involves Power Query using column headers to reference each column. When the column headers change in the source data, it gives us a big problem. Recently, my friend, Celia Alves released Read More

Posted in Blog, Power Query 1 Comment
How to split cells in Excel

How to split cells in Excel

Posted on March 26, 2021September 3, 2021 by Excel Off The Grid

There are many circumstances where we receive information with multiple data points inside a single cell. This often occurs when the data’s original intention is slightly different from how we intend to use it. In these circumstances, we often need to split a cell into its constituent parts. This post will look at solving this Read More

Posted in Blog, Formulas, Power Query, Tips & Tricks 2 Comments
All possible combinations from multiple lists in Excel

All possible combinations from multiple lists in Excel

Posted on February 14, 2021March 20, 2021 by Excel Off The Grid

Imagine a scenario where you have two or more lists in Excel and want to create a single table of all possible combinations. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost Centre combinations?”.  Sounds simple, right?  There must be a Read More

Posted in Blog, Power Query 6 Comments
Posts navigation
Older posts →

Awards

Microsoft MVP:


Acquisition International:
Business Excellence Awards 2021

Spreadsheet Instructional Expert of the Year

Where to find me

YouTube:

 

Twitter:
Follow @exceloffthegrid

Books

100 Excel Macros Book

Tell me more…

Recent Posts

  • Weighted Average in Excel
  • Power Query – Connect to OneDrive Personal files
  • Power Query – Connect to OneDrive or SharePoint
  • Unblock Macros downloaded from the internet
  • Using an Excel Table within a data validation list
Theme by Out the Box
  • Home
  • Blog
  • Courses
  • Books
  • About
  • Terms and conditions
  • Privacy policy
  • Affiliate disclosure
  • Contact

Special Offer: Join the Automation Academy for $5.
Limited time only

Join now!
X