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
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 🙂