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

December 18, 2014 / Erik Svensen

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Expand this

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

Then I create a calculated timer column

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

Where I get a time for each Intensity.

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

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

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

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

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

Link the X and Y to the X and Y Coordinate

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

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

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

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 icon smile Decorating the X mas tree using Power Query, Power Pivot and Power Map #PowerBI

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

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 icon smile Decorating the X mas tree using Power Query, Power Pivot and Power Map #PowerBI

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.
Want something else to read? How about ‘Grievous Censorship’ By The Guardian: Israel, Gaza And The Termination Of Nafeez Ahmed’s Blog

Erik Svensen