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:
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 Input
- 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.
- GetColumnNames: returns a list object which contains the names of all of the columns in the table returned in the Source step.
- 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:
- 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.
I’m sure this is going to be useful to me at some point in the future… You can download the sample workbook here.
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.
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.
I’ve been kicking around this idea trying to solve a problem without any luck. I am grabbing data from:
http://www.shootforum.com/bulletdb/bullets.php?cal=0.224 (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?
Yes, this is a pretty common problem to run into when you’re learning Power Query – there is no way to create a loop, and in fact you don’t need one. What you need to do is to create a function to grab data from your page, then create a table of all the values you want to pass to that function, then call the function in a custom column and finally expand the result. Matt Masson has a great post explaining this here: http://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/
Thanks for your all posts! Actually, this is my fist comment and it will be great if I could receive a reply.
For this particular question, I suggest using the third parameter of function ‘Table.TransformColumns’, which will make the whole codes reduce to ‘= Table.TransformColumns(data,{},each _+1)’ where ‘data’ means the original data table.
I know that you posted this for many years ago. I’m not sure that whether you have this feature in the year of 2014, but I just want to help you renew it.