Decorating the X-mas tree using Power Query, Power Pivot and Power Map #PowerBI

I really feel like the Power BI has supplied us with many nice presents during the year – by delivering the rapid development of the Power BI tools.

So why not try to pay a little back by using some of the tools to create a solution to decorate the Christmas tree with lights.

Of course, we need some data to do this – so I build a table in Excel to enter the information

You can enter the start time and interval of adding the lights and set the duration of adding the light.

In the color column, you can add the color of the light.

The lights will be positioned according to the X and Y coordinates – and in order to get them right I used Paint to get them

In this example – 263, 493 as you can see in the status bar.

Next up – Start power map in Excel – and use the recently added new Custom map feature

Remember to select Pixel Space so the max for x and y is set to the dimensions of the picture.

In order to make the lights appear one by one we have to use the timer function in Power Map but then the data will have to contain a record for each time – and for that we use Power Query to create a list of records to make the lights appear slowly.

So switch to Power Query and use the table as source

But I want each light to appear slowly – so I need to have number of rows for each light as stated in the Max column – in order to do this I use the List.Numbers function

For each row I then get a list of values from 1 to Max

Expand this

Then I create a calculated timer column

Where I get a time for each Intensity.

Add this to the datamodel of the Workbook and add a measure to sum the Intensity

Now we have the data ready to decorate the tree in Power Map

Link the X and Y to the X and Y Coordinate

Add the sum of intensity to Size, the color as Category and Timer as Time.

On the Layer options in the settings area – change the size and thickness in order to adjust the sizes – and in the color choose the right combinations of Color category and Map color.

And then you are ready to decorate the x-mas tree 🙂


Here is a link to the file – the file

And the video – Link

And Merry Christmas to all – and especially to the Power Bi Team 🙂


One thought on “Decorating the X-mas tree using Power Query, Power Pivot and Power Map #PowerBI

  1. Pingback: Setting selected item in a slicer connected to a Power Pivot table via VBA | Business Intelligence Info

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s