Power Query M Language Editor

Power Query (Get & Transform) uses the M language to allow you to customise and build in flexibility to your queries.

Currently writing the M language is tricky to write. It is case sensitive and there is no built-in help to assist you using the language.

Apparently Microsoft is working on creating an M editor, but there is no time line on when it will be ready.

This article takes you through some steps to create your own editor using Notepad ++.

If you use Power Query a lot then this may be worthwhile.

Pasting a Filtered List in a Formatted Table

I have been recently working with some very large (500,000+ rows) tables. As part of the process I had to filter one Formatted Table, copy it and then paste it in another Formatted Table. Excel would sit there processing for a long time – but I found a technique to speed up the process.

After you copy the filtered list, simply paste it in a blank sheet. This is virtually instantaneous. Then copy that interim list and paste in the other Formatted Table – again almost instantaneous. Two quick pastes is a lot quicker than paste and wait.

In case you didn’t know, when you copy a filtered list, you only copy the visible cells – the filtered ones. The hidden cells are omitted from the copy.

So if you are experiencing delays in the pasting of a filtered list, just use an interim paste and then another copy to speed up your copy and paste.

For more information on Formatted Tables, check out the links below.

Format As Table in Excel Part 1

Excel Format as Table Part 2 [VIDEO]

 

Inserting Data into Formatted Tables

I was recently working with a large Formatted Table in excess of 100,000 rows with Power Query.

I was copying in new data to a temporary workings table and then manipulating it with Power Query to get the required output. The data was varying lengths. I found that if you pasted data into a Formatted Table that was a lot longer than the Formatted Table it can take a long time for Excel to process the paste (I am talking tens of thousands of extra rows).

To get around this delay I found that if you first expanded the Formatted Table using Insert Rows, the paste was virtually instantaneous. Inserting the extra rows was also very quick.

So if your Table has sufficient rows the paste is quick, if Excel needs to expand the table to fit the new data, it can be slow for large data sets. Make sure you insert sufficient blank rows to speed up the paste.

You can learn more about Formatted Tables at the two blog posts below. I have also covered the topic in numerous free webinars.

Format As Table in Excel Part 1

Excel Format as Table Part 2 [VIDEO]

Relationships Shortcut

Excel 2013 added the Data Model to Excel.

The Relationships option (Data ribbon) is part of that model. It allows you to create relationships between tables so that you can use a PivotTable to report on multiple tables.

See my December 2016 INTHEBLACK article for an example.

The keyboard shortcut to create or edit, a Relationship is easy to remember – it is

Alt a a

Pressed in sequence, not held down.

Excel Cell Comments and Documentation

Macro to Create a Comment Report for a File

I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.

Reapply a Filter

Let’s say you have applied a filter to a list.

The list is dynamic and the values have been updated by a refresh – the filter is out of date.

You don’t have to go into the filter drop down to re-apply the filter.

You can use a keyboard shortcut whilst in any cell in the table.

Ctrl + Alt + L

 

reapply

ExceleratorBI

Matt Allington is an Aussie who is a Power BI expert and spreading the word on  Power BI.

This is his blog, but the rest of his site has other resources as well.

His site has lots of content to help you dive into Power BI.

I have his book and am currently going through it.