Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

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

with 2 comments

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:

image

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

image

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:

let

    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)

in

    Output

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:

    image

  • 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.

Written by Chris Webb

April 16, 2013 at 1:58 pm

2 Responses

Subscribe to comments with RSS.

  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.

    Bob Phillips

    April 16, 2013 at 2:30 pm

    • Thanks Bob – what I meant to say was that you could do this by creating custom columns in Data Explorer. But it’s good to know the Excel equivalent too.

      Chris Webb

      April 16, 2013 at 2:49 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 3,190 other followers

%d bloggers like this: