Power Query Zoom

I used this today in a live webinar.

I zoomed into the Power Query window in Excel to make it easier to see.

Ctrl + Shift + + (plus)

Excel Power Query and Multiple Files [one hour video]

Includes detailed pdf manual

Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.

Power Query can automate the importation process. It is now a vital skill to possess.

This is a follow up session to the Introduction session which you can view here.

This session focuses on importing multiple files from a folder. The session uses CSV and Excel files plus Excel tables.

This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.

(Apologies I did have a coughing fit – I thought I had turned off the microphone but I hadn’t.)

Use the button below to download the pdf manual and example files so you can follow along.

We need to get the word out on this powerful Excel feature.

(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)

Download Materials

Introduction To Excel Power Query [one hour video]

Includes detailed pdf manual

Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.

Power Query can automate the importation process.

It is now a vital skill to possess.

This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.

Use the button below to download the pdf manual and example files so you can follow along.

We need to get the word out on this powerful Excel feature.

(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)

Download Materials

Power Query shortcut for Adults

The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only!

To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down.

So only adults allowed in Power Query.

Related Posts

 

Power Query feedback

Received this email after a recent Power Query session – some things may take a few goes to understand, that’s OK, that’s how we learn.

Free Webinar Recording – Introduction to Power Query

Feedback score 94.5% based on 91 responses

In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network.

The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video.

Download Materials

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources. Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

This session covers

  • fixing dates so that Excel can recognise them
  • formatting columns as text – retaining leading zeroes in CSV files
  • deleting unwanted rows and columns from your data
  • removing leading and trailing spaces
  • populating blank values with zeroes
  • populating blanks with entries from above
  • correcting trailing minus signs
  • unpivot a report – how to convert a report layout into a data table layout
  • converting a MYOB report into a data table

DAX Functions

A listing of all the DAX functions with their syntax.

DAX functions are used in Power BI and PowerPivot for Excel.

It is a reference site, not a training site.

It has been created by Marco Russo and Alberto Ferrari – legends in Power BI and all things DAX.

Link to DAX Guide

 

Creating a table of all combinations in Excel [Video]

Power Query solution

Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.