Applying a Function to Every Cell in a Table in Data Explorer (Power Query)

NOTE: This post was written before Data Explorer was renamed as Power Query. All of the content is still relevant to Power Query.

Now that the PASS Business Analytics Conference is over, I can get back to playing around with Data Explorer and blogging about it. I’ve been working on a fun demo that I’ll try to blog about later this week, but in the course of creating this demo I came across a technique that I didn’t end up using but which I thought deserved a post on its own: how to apply a function to every cell in a table, rather than just every cell in a column.

For example, let’s imagine that you have a table that looks like this:


…and you want to add one to every single cell in the table, so you get:


It’s possible in the UI by creating lots of custom columns and then deleting the original columns, for sure, but I found a more elegant solution. Here’s the full DE code:


    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    FunctionToApply = (x) =>; x + 1,

    GetColumnNames = Table.ColumnNames(Source),

    TransformList = List.Transform(GetColumnNames, each {_ , FunctionToApply}),

    Output = Table.TransformColumns(Source, TransformList)



Here’s what each step does:

  • Source: gets the data from the Excel table named Inputimage
  • FunctionToApply: defines a new function that takes a single parameter, x, and returns the value x+1. See this post for more details on using function in Data Explorer.image
  • GetColumnNames: returns a list object which contains the names of all of the columns in the table returned in the Source step.image
  • TransformList: this is the interesting step! It creates a new list based on GetColumnNames, but whereas GetColumnNames contains just one record per item in the list (the column name), this returns a list of lists, each with two items: the column name and a reference to the function FunctionToApply. It looks like this in the UI:imageIf you click on the first of the list links shown (and this isn’t part of the process, I’m only doing this to show what’s there) you see the following:


  • Output: the list of lists created in the previous step can now be passed to the Table.TransformColumns() function to apply the function FunctionToApply() to every cell in every column in the table.image

I’m sure this is going to be useful to me at some point in the future… You can download the sample workbook here.

6 thoughts on “Applying a Function to Every Cell in a Table in Data Explorer (Power Query)

  1. Interesting post Chris; can’t think of uses off-hand but I am sure this could be useful.

    As an aside, an Excel Pro would never create a lot of extra columns to do something like this, there is a much simpler solution in the UI. Taking your example, we would :
    – enter a 1 in a spare cell somewhere
    – copy the cell with the 1 to the clipboard
    – select the target cells(contiguous, non-contiguos, table with or without headers)
    – Home>Paste>Paste Special and select the Add option in the Operation area
    – now clear the cell with the 1.

    You can add, subtract, multiply, divide very easily using this method.

  2. I’ve been kicking around this idea trying to solve a problem without any luck. I am grabbing data from: (for example).

    The returned data is tabular, with a selector, but the Power Query engine doesn’t recognize it and returns only a Document entry. I’ve dug around in the data and gotten to a point where I have extracted the headers into a table, and I can successfully extract a single row of data and merge them. When I try to get to the next step (iterate through the results and append each) I hit a wall. No loop function, and seemingly no way to construct a row pointer to step through. Have you solved this type of issue before?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s