Creating dynamic lookup-tables with unique values using Power Query instead of a database

March 4, 2014

 

Guest post by Lars Schreiber

Hello PowerPivot Community,

I’m quite sure most of you know a scenario comparable to the one in the following figure:

 

Bringing two fact-tables with different date-columns together using a 'unique-months'-dimension

Two scenarios (in two tables) – in this case actuals and budget – have to be put together to do some math on them. The only problem you have is the different level of planning regarding the time dimension. While the actual figures are on daily basis, the budget was planned on monthly basis. As you could learn on this website many times before (e.g. here) you need another lookup-table with a unique list of months to bring both tables (actual & budget) together. And this is where Power Query can help you a lot.

 

Read the rest of this entry »


What Excel users could learn from Masterchef or why preparation is so important!

August 22, 2013

 
Intro from Rob:  In the car today I realized two things.  One, I have been a terrible host.  Multiple guest posts got stuck in the pipeline while I focused on PowerPivotPro School.  Much effort went into these articles and I apologize to all of you who have been waiting patiently to see them shared.  My original post idea for today will wait.

Today we start with Anne Walsh, who you will see employs a very different “lens” than the one typically used by yours truly.  Except for her movie and pop culture tie-ins – those are quite consistent!

The 2nd realization was triggered by Anne’s references to Ratatouille.  Remember the line “anyone can cook?”  And how that rankled the elitist chefs?  There’s a strong parallel there in my belief that “anyone can business intelligence.”  As long as they savvy Excel, that is :)  Onwards…

What Excel users could learn from Masterchef or why preparation is so important!

By Anne Walsh

colette_ratatouille

 

 

Colette: [Linguini is making a mess at the kitchen] What is this? Keep – your station – clear! When the meal rush comes, what will happen? Messy stations slow things down. Food doesn’t go, orders pile up, disaster! I’ll make this easier to remember: keep your station clear, or I WILL KILL YOU! (Taken from Ratatouille)

http://www.imdb.com/title/tt0382932/quotes?item=qt0465201

 

Fruit stall | store in Tai O, a fishing town located at western side of Lantau Island in Hong Kong.

Taken by Denis Won

 

Before we begin, let me point out that while I respect Colette’s fierce commitment in Ratatouille, I am not espousing her approach to her co-workers!  But if you have ever had to start doing something that required a well organised list only to find that it was actually all over the place – you may have some empathy for her..

In this side of the world, (Europe), we have a programme called Masterchef (UK and Ireland)  in which amateur cooks are asked to prepare dishes which are then evaluated by two master chefs.

However I always notice that a key part is glossed over. The chefs work in an environment with great well prepared ingredients and top notch equipment. You don’t see them running out because someone forgot the vanilla essence…

Alas in the Excel world, we start well before that. We usually don’t have any minions to get our saucepans, knives and source perfectly shaped tomatoes. Usually we have to assemble our lists (often with blemishes and long gone sell by dates) so this is where I want to start. We have to go to the market to get the ingredients, and prep them.

Read the rest of this entry »


New Version of Data Explorer Released

March 26, 2013

 
Quick post today, as I am travelling again.  PowerPivot’s version of Tyler Durden Smile

If you installed Data Explorer a couple weeks back, go look at your Data Explorer ribbon tab today:

image

This “Update” Button “Lit Up” Last Week – Click It Smile
(I already did, so mine is now disabled again)

That will launch you into a surprisingly painless update.  Pretty nifty.

New Features

They added support for a few new data source types:

  1. Azure HDInsight – the MS cloud version of Hadoop
  2. Teradata
  3. PostgreSQL

They also improved the query editing experience.

Full details here:  http://blogs.msdn.com/b/dataexplorer/archive/2013/03/12/a-new-build-of-data-explorer-and-an-auto-update-feature.aspx

Download it From Scratch

If you haven’t downloaded data explorer yet, you can get it here:

http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

What the Heck is Data Explorer?

Read this quick post from two weeks ago


“Introducing” Data Explorer

March 7, 2013

 
image

In My Opinion, There Aren’t Many Things More Exciting Than a New Ribbon Tab Full of Goodies
(And One That I Can Add to Excel 2010 or 2013 – I Hear That’s Important for Some Reason)

Maybe There’s a Future in this “Numbers” Thing…

It does seem like Microsoft has figured out that data is a big deal.  Every time I turn around, I am hearing of a new software development team joining Microsoft’s efforts in the Excel/BI/Overall Data Crunching space.

Often, such teams are merely whispers – shadowy rumors on the wind.  Friends disappear from their familiar roles and reappear in places they can’t talk about.

Other times, someone new to me walks up and just hands me a piece of nearly-finished software.

This is one of those latter cases.

Some Highlights

I don’t have time for a full tour today, and honestly I haven’t even explored all of the functionality yet.  So let’s hit some highlights shall we?

Read the rest of this entry »


Friday Bonus: Database Pros In Demand

January 4, 2013

 
image

In a PowerPivot Environment, A Single DB Pro
Can Empower MANY PowerPivot Pros

Short Version:  Job Openings in the UK for SQL Pros

I’ve recently been asked to post about some opportunities for SQL pros in the UK – opportunities in which qualified candidates would be working closely with Excel Pros in a PowerPivot environment.  A situation that closely resembles what it’s like to work as a SQL pro at Pivotstream, or as a SQL pro working at virtually any of my training clients (who end up implementing similar collaborative systems).

Scroll to the end of this post for the details, but first, I want to provide some context – both for interested candidates as well as for all readers.

Better With a Database:  a Recurring Theme

For a long time now, I’ve been saying that you can get a LOT more out of PowerPivot if you pair it with a database.  For reference, see the following posts:

  1. Self Service BI Doesn’t Mean Desktop Standalone BI (one of my most popular all-time posts, at least in certain circles)
  2. Better With a Database Part One
  3. Better With a Database Part Two

The human element, and why “anthony” is becoming a noun

Stated even more clearly, an Excel Pro can achieve much better results when they collaborate with a Database Pro.  (Yes, sometimes they can be the same person, but generally speaking, the skillsets are distinct).

Read the rest of this entry »


Sign Up With Connection Cloud, Get My Book for Free

December 14, 2012

 
image

Connection Cloud is Offering to Buy You a Copy of My Book, Click Image for More Info

The cloud data connection service I talked about and demo’d in yesterday’s post is offering to buy a copy of the book for anyone who sign up with Connection Cloud by January 15th.

A flattering gesture on their part, this is definitely a first for me Smile

Excel Pros – the world is waking up to our importance.


Cloud Data Approaching Critical Mass: Connection Cloud, SalesForce, PowerPivot, & Webinar on YouTube

December 13, 2012

 
image

Cloud Data Like SalesForce Available to PowerPivot as if it Were in a Local Database:
My Long Wish for a “Data Highway” Gets Closer Every Day
(Click for the Webinar Featuring Yours Truly on YouTube)

Flashback 2001:  The “Data Highway” Concept

Back at Microsoft in 2001 when I was working on what eventually became Excel 2003, I pitched a vision that I called “Data Highway.”  (OK, not an original name considering the Information Superhighway thing coined by Internet inventor Al Gore, but invention is smart and theft is genius, or something like that.)

The idea behind Data Highway was simple:  all relevant data made available to the most popular tools (cough cough Excel), in a convenient and refreshable format.  No manual gruntwork required to “fetch” data in other words – saving your brain for actual thinking.

imageThere were three elements to the pitch:

  1. A common internet protocol for exchanging data. 
  2. “Teaching” Excel, Access, and other tools to consume any data source exposed via that protocol.
  3. A marketplace for data where providers like Dun and Bradstreet could sell data to be piped straight into Excel.

Well the protocol flopped and our VP killed the marketplace idea before it got off the ground.  Having good ideas isn’t enough – you can’t be too early, and you also need to execute better than we did.

Fast Forward to Today

Here we are at the end of 2012, and we have all three elements available in different (but robust and real) forms:

Read the rest of this entry »


Webinar Wednesday on Cloud-Based Dashboards

December 4, 2012

image

Today’s “post” is actually going to be a series of small updates/announcements.

Connection Cloud is one of our new partners at Pivotstream.  They do something amazing:  they make your cloud data sources like SalesForce and Facebook look like normal databases.

Which means PowerPivot can connect to those sources and pull data just as easily as you pull data from a database like SQL or Access:

Read the rest of this entry »


Combine Multiple Worksheets/Workbooks into a Single PowerPivot Table

September 18, 2012

One of those simple but indispensable tricks

Back to a “real” post now after all the book stuff, but it’s going to be a short one while I get back on my feet.

Let’s say you have multiple worksheets (or workbooks) that all contain the same sort of data:

image  image  image

Multiple Worksheets (or Workbooks), All Contain The Same Type of Data

You Want to Combine ALL of Them Into a Single PowerPivot Table

These worksheets all come to you separately, but really you just want them as one big table.

Naturally, if it’s a small number of sheets, and each sheet isn’t massive, you can just copy paste them all into one table in Excel, then copy/paste into PowerPivot, or link the table into PowerPivot, or export as CSV so you can import it.

And you could also use Paste Append to directly paste into PowerPivot.

But if the combined data set exceeds 1 million rows, you won’t be able to combine the sheets into one – you will exceed the worksheet row limit.  And a data set of that size is not something you can paste into PowerPivot directly with Paste Append – pasting large data sets into PowerPivot takes forever, if it completes at all.

Here’s what I do when I find myself in this position:

Read the rest of this entry »


Monday Bonus: SalesForce Data Imported into PowerPivot Without Programming?

July 30, 2012

 
SalesForce.com data loaded into PowerPivot (No Special Skills Required)

SalesForce.com data loaded into PowerPivot (No Special Skills Required!)

This Whole Cloud Thing Just Might Catch On…

We live in pretty exciting times.  Sometimes it’s simply amazing what I can do from my desk, without having to take off my Excel hat.  All of these various technologies available to us in the cloud, plus PowerPivot’s ability to talk to them…  the net impact really starts to add up sometimes.

All of the Tables Available, Just Select and Click Finish

I didn’t have to do anything “technical” to pull this off really, I just end up using the PowerPivot import wizard:

Simple import of Salesforce.com data into PowerPivot, just select and click

Simple import of Salesforce.com data into PowerPivot, just select and click

What’s the Trick?

Read the rest of this entry »


Excel 5-Calendar Date Table

May 17, 2012

Guest Post by Colin Banfield [LinkedIn]

image

For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a “portable” date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch.  Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the “Ultimate Table” lacks fiscal periods. Much of the analysis work I do includes fiscal periods.

Read the rest of this entry »


Workarounds for “Canceled Due to Memory Pressure”

April 24, 2012

 
Ah, the dreaded dialog:

The Operation Has Been Cancelled Due to Memory Pressure

The Operation Has Been Cancelled Due to Memory Pressure

You can run into this error via a number of different routes, but one way or another, your computer ran out of memory while PowerPivot was trying to do something.

I’ll list some potential fixes here:

Read the rest of this entry »