Technology

Microsoft Power Query for Excel Tips and Tricks

by

In this post, I’ve documented a few Microsoft Power Query for Excel tips and tricks that I’ve learned from a recent project. For all of the following examples, I have an Excel table named TEST_DATA containing two columns (Description and Amount). The Power Query add-in for Excel can be downloaded from Microsoft.

Test Data in Microsoft Power Query for Excel
Test Data in Microsoft Power Query for Excel

Improving Performance

Ignore Privacy Level Settings

On the Power Query ribbon, click the Options button. The Query Options dialog box is displayed. Click the Privacy option, select Always ignore Privacy Level settings, and click the OK button.

Unless you have a specific reason to keep privacy settings active, you may gain some performance improvements by ignoring them. I didn’t notice much of an improvement, but it may depend on the volume and complexity of the data set involved.

Privacy Level Settings in Microsoft Power Query for Excel
Privacy Level Settings in Microsoft Power Query for Excel

Retrieve Source Data Once

When I started using Power Query, I created an initial query and then duplicated it each time I needed to start a new query. I would then modify the duplicated query to meet the new reporting requirement. All of these queries used the same source data which was a table on another worksheet. These weren’t complicated queries, but around the 15th query created in this manner, Power Query became sluggish during refreshes. The application would eventually throw Out of Memory exceptions and Excel would crash. The data source was a simple 100+ row worksheet in the same workbook. Again, nothing complicated.

Each of those queries started with a number of common steps, e.g. define the source data, remove some columns, and filter some data. After realizing my sloppiness, I defined a new query that handled all of these common steps. I then modified the existing queries to remove those common steps and instead refer to this base query as the starting point. Let’s assume that I named the base query as BASE_DATA. In each of the other queries, the source data is then established with the following statement: = #”BASE_DATA”

Once these common steps were pulled into a single, standalone connection query, then the Out of Memory exceptions stopped and the refreshes were substantially faster.

The BASE_DATA query is defined as:

let
Source = Excel.CurrentWorkbook(){[Name="TEST_DATA"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Amount", Int64.Type}})
in
#"Changed Type"

Tips and Tricks

I’ve started a new query named EXAMPLE_QUERY to demonstrate the following tips and tricks. Again, it will use the TEST_DATA table data by way of the BASE_DATA query defined above.

Our first step is to establish the source data: = #”BASE_DATA”

Creating Total Rows

I know that a total row can be easily added to an Excel table through the standard Excel table options. However, what if we wanted to add one using Power Query? Through the Table.Combine and List.Sum functions we can combine all of the records from the source data with a calculated total row created using Table.FromRecords.

= Table.Combine({#"BASE_DATA", Table.FromRecords({[Description="Total", Amount=List.Sum(#"BASE_DATA"[Amount])]})})

This step combines all of the rows from #”BASE_DATA” and, by using the Table.FromRecords function, creates an in-line table with one record. This in-line table sets the Description column to the text Total and the Amount column to the sum of all entries in the #”BASE_DATA”[Amount] column. I found this useful when creating a query where the base data was used in a waterfall / bridge chart.

Creating a Total Row Using Microsoft Power Query for Excel
Creating a Total Row Using Microsoft Power Query for Excel

Creating Running Totals

The next example adds a column to calculate a running or accumulating total for each of the rows. The first step is to create an Index column starting from zero. If you create an Index column starting from 1, then you would need to adjust the second step accordingly. The #”Create Total Row” is the name of the previous step.

= Table.AddIndexColumn(#"Create Total Row", "Index", 0, 1)

Next, we add another column to contain the running total. Notice that #”BASE_DATA”[Amount] is used in the List.FirstN function. This is to avoid including the total row in the running total. If you used #”Added Index” (the default step name from adding the Index column), then the running total would incorrectly include the total row. Also, if you set your index to start at 1, then you would need to change the [Index]+1 parameter to [Index].

= Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"BASE_DATA"[Amount],[Index]+1)))
Creating a Running Total Column Using Microsoft Power Query for Excel
Creating a Running Total Column Using Microsoft Power Query for Excel

Using Data from the Previous Row

In this example, I create a new column that adds the value of the Amount column from the previous row to the value of the Amount column in the current row. The if conditional handles the first row (Index equals zero) where there is no previous row. This is for demonstration purposes only so the formula doesn’t particularly make sense to do in real life. The previous Amount column value is obtained with the statement #”Added Running Total”{[Index] – 1}[Amount].

= Table.AddColumn(#"Added Running Total", "Previous Plus Current", each (if [Index] > 0 then #"Added Running Total"{[Index] - 1}[Amount] else 0) + [Amount])
Accessing Previous and Current Row Values in Microsoft Power Query for Excel
Accessing Previous and Current Row Values in Microsoft Power Query for Excel

Filtering Based on a Named Cell Value

Let’s say that you want to filter the data based on the value of a cell somewhere in your workbook. As the first step, you would need to define a named range for that cell. In this example, I’ll define a cell named FILTER_VALUE. To get the value of that cell from within a query, you can use a custom Power Query function which we’ll create called GetNamedCellValue. This query function has one parameter named rangeName and it is created as a connection only query.

= let GetNamedCellValue=(rangeName) => 
  let
    content = Excel.CurrentWorkbook(){[Name=rangeName]}[Content],
    value = content{0}[Column1]
  in
    value
  in GetNamedCellValue

Once defined, you can use this function in another query as follows:

= Table.SelectRows(#"Added Previous Plus Current", each [Amount] > GetNamedCellValue("FILTER_VALUE"))

Handling an Empty Table

Continuing with the same query, I add a filter to intentionally return an empty table to demonstrate this example. The source data doesn’t have any entries with an amount greater than 1,000 so I filter the column to show only entries with a greater amount.

= Table.SelectRows(#"Added Previous Plus Current", each [Amount] > 1000)

If you’re using the custom GetNamedCellValue function defined earlier, you can set the FILTER_VALUE cell to 1,000 and use the following step instead:

= Table.SelectRows(#"Added Previous Plus Current", each [Amount] > GetNamedCellValue("FILTER_VALUE"))

As expected, the filter step returns an empty table. In the next step, I check if I have an empty table using Table.IsEmpty and then combine the empty table with an in-line table where the Description column is set to No entries found. If the table is not empty, I return the results from the previous step with no modification. I normally include this step at the end of most queries so that when a query is loaded to a worksheet, it is clear to users that the table is empty because of the data and not because of an error.

= if Table.IsEmpty(#"Filtered Rows") then Table.Combine({#"Filtered Rows", Table.FromRecords({[Description = "No entries found."]})}) else #"Filtered Rows"
Adding a Message to an Empty Table in Microsoft Power Query for Excel
Adding a Message to an Empty Table in Microsoft Power Query for Excel

Summary

The full EXAMPLE_QUERY query used in the above examples is:

let
Source = #"BASE_DATA",
#"Create Total Row" = Table.Combine({#"BASE_DATA", Table.FromRecords({[Description="Total", Amount=List.Sum(#"BASE_DATA"[Amount])]})}),
#"Added Index" = Table.AddIndexColumn(#"Create Total Row", "Index", 0, 1),
#"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"BASE_DATA"[Amount],[Index]+1))),
#"Added Previous Plus Current" = Table.AddColumn(#"Added Running Total", "Previous Plus Current", each (if [Index] > 0 then #"Added Running Total"{[Index] - 1}[Amount] else 0) + [Amount]),
#"Filtered Rows" = Table.SelectRows(#"Added Previous Plus Current", each [Amount] > GetNamedCellValue("FILTER_VALUE")),
#"Create No Entries Found Message" = if Table.IsEmpty(#"Filtered Rows") then Table.Combine({#"Filtered Rows", Table.FromRecords({[Description = "No entries found."]})}) else #"Filtered Rows"
in
#"Create No Entries Found Message"

Display the File Last Updated Date Using jQuery in SharePoint

by

In this example, assume there is a dashboard page on a SharePoint site that displays a number of graphs and charts. This dashboard is based on data contained in a single file located in a document library on the same site. Whenever this file is updated, the last modified date of the file needs to be automatically reflected on the dashboard page. This allows the page viewer to know the freshness of the dashboard data without requiring the content owner to edit the dashboard page itself with every data change. The following code will display the file last updated date using jQuery, the SharePoint Client Object Model (sp.js), and a bit of HTML. The formatDate function is used to format the file last modified timestamp in dd-mon-yyyy format.

Add a Content Editor Web Part to the dashboard page and include the following script in the source (or as a reference to a script file).

Change the FileUrl variable in getFileInfo() to the appropriate file path. When the code executes, it will replace the contents of the “data_last_update_date” container with the formatted last modified timestamp.

<script src="http://code.jquery.com/jquery-latest.min.js"></script>

<script type="text/javascript">
  function getFileInfo() {
    var clientContext = SP.ClientContext.get_current();
    var web = clientContext.get_web();
    var FileUrl = '/path/to/file.txt';
    var file = web.getFileByServerRelativeUrl(FileUrl);

    clientContext.load(file);

    clientContext.executeQueryAsync(
      function() {
        $('#data_last_update_date').html("Data Last Updated: " + formatDate(file.get_timeLastModified()));
      },
      function(sender, args) {
        console.log('Function getFileInfo() failed: ' + args.get_message());
      }
    );
  }

  function formatDate(timestamp) {
    var month_names = new Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
    var pad = "00";

    if(timestamp) {
      var d = new Date(timestamp);

      return (pad + d.getDate()).slice(-pad.length) + "-" + month_names[d.getMonth()] + "-" + d.getFullYear();
    }
  }

  $(document).ready(function() {
    ExecuteOrDelayUntilScriptLoaded(getFileInfo, "sp.js");
  });
</script>

<p> </p>
<div id='data_last_update_date' style='text-align:center; width:100%;'></div>

Stacks on Stacks…of Floppy Disks

by

There is an episode of White Collar called Uncontrolled Variables where a company uses 8-inch floppy disks to store and secure sensitive information. The premise is that the 8-inch storage medium and file formats are so old and obsolete that no one would be able to access the contents of the disk. While it makes for an entertaining episode, I wouldn’t use this method to secure my information.

Meanwhile, back in real life, I found stacks of 3½-inch floppy disks sitting in a box untouched for 20 years. The labels had been crossed off and rewritten multiple times over the years. Do I really want what’s on a disk labeled “MS-DOS 6.0 Backup 7 of 16”? I couldn’t trust the labels and I was concerned that the contents may include information that remains sensitive over long periods of time such as personally identifiable information.

No big deal, right? I’ll pop the disks into my computer’s disk drive and start reviewing. Oh — I didn’t put a floppy drive in my machine when I built it. I’ll try the laptop. No floppy drive there either. Hmm… Maybe I’ll use the disks to play dominoes. (If you’re wondering, I tried and I couldn’t get them to stand upright on their own.) Luckily, 3½-inch floppy disk readers are still readily available online and at a reasonable cost. I ordered one of these drives and, when it arrived, I went to work attempting to read the disks.

3½-inch Floppy Disks
3½-inch Floppy Disks

While the initial problem was solved, a new problem emerged. I realized immediately that most files on the disks were 20 to 25 years old (obviously since the disks hadn’t been touched in that long). The second observation was that a surprisingly large number of files could be stored on a single disk with a mere 1.44 megabyte capacity. Through another stroke of luck, most of the files were in a version of the WordPerfect file format readable in Microsoft Word. With other files, I had to look at the binary and do a little research to identify the format. In many cases, these files were also saved without file extensions or the extensions were nonsense. In the end, I was able to find utilities online to read and convert to more current formats. I was also amazed that most of the disks were still readable. Only a few disks had issues where I couldn’t access all of the files.

Given this experience, I certainly wouldn’t use 3½-inch disks as an information security solution proposed in White Collar. It’s still too easily accessed to provide the level of obstacle. Maybe 8-inch disks are better, but I’ll stick with physically secured offline encrypted drives.