Newly added Power Query specific help forum at Excelguru

I'm honestly not sure what's taken me so long to do this, but I'm pleased to say that I've finally added a Power Query specific help forum at Excelguru.  I'm hoping that this forum becomes THE place to ask and answer Power Query (or Get and Transform) related questions for both Excel and Power BI desktop.  After all, we wrote the book, so it only makes sense that we try and host the Q&A on the topic.  🙂

Extra monitoring of the Power Query Specific help forum

As the forum gets up and running there are a couple of key people I've added for email notification as well.  The intent here is that we get notified when people post questions, and will try to focus on making sure that they get addressed and (hopefully) solved.  If you are a Power Query expert and would like to be included in that list, just email me or post on this thread.  I'll get you set up.  (Make sure you've signed up for an account on the site, as I'll need your user ID to do this.)

Naturally, if you've got a question pertaining to the topic posted on the blog, you can still ask it here.  If the question is a bit more general though, I'd encourage you to sign up at www.excelguru.ca/forums and post the question in the Power Query forum.

Re-focusing on the Power Technologies

While I was setting this up, I also took the time to set up forums specific forums for some of the other "Power" stack:

Hope to see you there!

Replace Records Via Joins in Power Query

I got an email from a friend today who was using some complicated logic to replace specific records in a table with records from another table.  His query was running pretty slow, so he reached out for a little help. In this post I'll show how to replace records via joins in Power Query; a much easier (and what should be a faster) solution to his issue.

Data Background

The data footprint that was sent to me looked something like this:

image

And the desired output is shown below:

image

So basically, we want to take the record for Unit002 from the Override table and replace the Unit002 value in the Original Data table.

At first glance, this looks hard.  And my friend cooked up something pretty complicated to make this work.  Funny thing is (and believe me… I've had this happen to me as recently as last week…) when you put another pair of eyes on it, you suddenly realize it's much easier than you first saw.

In this case we can actually solve this very easily by using a couple of Power Query's different Join types!

Laying the Groundwork

If you want to follow along, grab the sample workbook here.  You'll notice that we have taken the following actions already:

  • Select any cell in the Original Data table
  • Create a New Query –> From Table
  • Go to Home –> Close & Load To… –> Connection Only
  • Select any cell in the Override With table
  • Create a New Query –> From Table
  • Go to Home –> Close & Load To… –> Connection Only

Which leaves us with the following queries in the Workbook Queries pane:

image

We are now set to replace the records.

Replace Records Via Joins in Power Query

This actually takes a Merge and an Append in order to complete the job.  So let's start at the merge.

  • Right click the "Original" query –> Reference

This creates a pointer to the data in the "Original" query, showing all four rows of data in the table.  The challenge here is that we only want the rows which are NOT being replaced.  The secret to getting those?  An Anti-Join!

  • Go to Home –> Combine –> Merge Queries
  • Choose the Override query
  • Select the Unit column on both the top and bottom queries
  • Change the Join Kind to "Left Anti (rows only in first)"

image

  • Click OK

At this point, you'll have 3 rows left, as shown below:

image

Why only 3 rows?  Because the Left Anti Join only returns the rows which don't match what is in the other table.  So where Unit002 exists in the second table, it cause it to pull everything EXCEPT Unit002 from the left table.  (For more on using Anti-Joins in Power Query, see this blog post.)

Joining tables does create a new column however, even if it is full of null values (as this one is.)  Since we don't need it, let's just delete that column:

  • Right click the NewColumn column –> Remove

Now we just need to add the record(s) from the Override table to this list.  That's fairly easy:

  • Go to Home –> Combine –> Append
  • Choose the Override table
  • Right click the Unit column –> Sort –> Ascending (this step is optional, and done for readability only.)

And you're done!  5 steps (after the connection only queries were created), 100% user interface drive, and should perform quite quickly. Smile

Running Totals using the List.Accumulate() Function

A while back I got an email from someone who had taken my Power Query training course online.  They were asking how to create a running total, although with some added twists and turns for calculating taxable gains and losses for a stock portfolio.  I decided to tackle that using the List.Accumulate() function.

Now, to be fair, I'm not going to demo the whole stock portfolio thing, but I do want to look at the List.Accumulate function as I found this a bit… confusing… to build.  It's super useful to be sure, but the help article… it needs work.

The Data

I'm using a pretty simple dialog box, inspired by my time in Australia.  You can download a copy from this link, but here's what it looks like:

image

Pretty simple, but now I want to create a running total that has 685 for Tim Tams, 741 for Stuffed Koala, and so on.

The List.Accumulate Function

So I headed over to MSDN, and found this helpful little article on the List.Accumulate function. It contains the following information.

Function:

List.Accumulate(list as list, seed as any, accumulator as function) as any

Arguments:

Argument Description
list The List to check.
seed The initial value seed.
accumulator The value accumulator function.

Example:

// This accumulates the sum of the numbers in the list provided.
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) equals 15

Using the List.Accumulate Function

So this formula looks pretty promising.  Let's go see how it works…

    • Click in the table of data –> create a new query –> From Table
    • Go to Add Column –> Add Custom Column
      • Formula Name:  Initial
      • Formula:

=List.Accumulate(
#"Changed Type"[Sales],
0,
(state, current) => state + current
)

The tricky part here is the #"Changed Type"[Sales], which provides the list of the sales values from the Changed Type step of the query (that was automatically created when we pulled the data in.)

And the result:

image

So this is a bit weird, as it shows the total for all rows, rather than the running total.  I figured that you should be able to change the accumulator function… except that there is no documentation about what the options are!  (I left some critical feedback on the MSDN site, and would suggest you do too, as that's pretty poor.)

At any rate, I tried dropping the "+ current" from the end, leaving just => state.  The result was a 0 value all the way down the column.  So that plainly didn't work. Then I tried modifying the formula again, leaving => current instead.  The result was 231 on all rows (so the last value in the accumulator.)  How 0 + 231 = 1095 I'm not quite sure but whatever.  state + current returns the overall total.

So plainly, we can't just use this function on it's own.

We need the List.Range function!

With the List.Accumulate function returning a total of all rows fed into it, it became plain that we needed to control what was being fed into the list used as a parameter.  So I reached back out to MSDN and browsed the site until I located the List.Range function.

Function:

List.Range(list as list, offset as number, optional count as number) as list

Arguments:

Argument Description
list The List to check.
offset The index to start at.
optional count Count of items to return.

Example:

List.Range({1..10},3,5) equals {4,5,6,7,8}

Using the List.Range function

In order to use the List.Range function, we are going to need to figure out which rows we want.  To do that, we need to add an Index column

  • Add Column –> Add Index Column –> From 1

Then add a column that makes use of List.Range()

    • Go to Add Column –> Add Custom Column
      • Formula Name:  Initial
      • Formula:

=List.Range(#"Added Index"[Sales],0,[Index])

So what I'm doing here is feeding in the Added Index step (from adding the Index column), and providing the [Sales] column to get a list.  But I'm asking it to return the list for the number of rows as contained in the [Index] column.  The result is a green word that says List all the way down the column.  But if I select the whitespace beside any of those List items, we can see what it is contained within.  Shown below is the list for the Stuffed Koala row:

image

Okay, so we now have a list of what we need…

Putting it all together

The final step is to put these together.  So let's add a new column again, but this time we'll use that List.Range() function instead of #"Changed Type"[Sales] as shown below

    • Go to Add Column –> Add Custom Column
      • Formula Name:  Success
      • Formula:

List.Accumulate(
List.Range(#"Added Index"[Sales],0,[Index]),
0,
(state, current) => state + current
)

And the result gives us what we were originally looking for:

image

The only thing left to do is remove the columns we used along the way.  Of course, we could just remove those steps, as they never really needed to happen, but I'm going to select them and remove them so that you can see the work in progress.

And sure enough, we get what we need!

image

Live Power Query and Power Pivot Training in Melbourne: Next week!

I know that this comes with limited notice but… as many of you know I'm currently in Sydney, Australia, and I'll be in Melbourne in a couple of days for Excel Summit South.  Well, as it happens, I'm actually staying in Melbourne for another week to deliver some live Power Query and Power Pivot training for a client.

Well guess what… we still have a bit of room, so we are going to open it up to the general public.  If you're interested in a full day of hands on training on either Power Pivot or Power Query, check out what we are doing at Parity Analytic's website, or download the individual brochures here:

(Registration information is included in the links above)

I'm very much looking forward to being able to share with a few more people, and hopefully you can be one!