Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Pretty Patterns With Power Query And Power Map

with one comment

Here’s something with no practical use whatsoever. Today, after I finished writing the first draft of the chapter on M of my upcoming Power Query book, I got thinking about how Power View and Power Map get all the attention because of all the eye-catching demos you can create with them. And then I thought – why bother spending time finding real data for these demos when you can generate artificial data in Power Query to create patterns? So I got to work…

As you probably know, you can create animated charts in Power Map so long as you have date-based data. I therefore created a function in Power Query to draw a circle as a series of points on a graph where each point is associated with a date; I also added data for height and colour for each point. Here’s the function definition:

let

    //declare function to draw a circle

    CircleFunction = (CircleRadius as number, StartDate as date, Reverse as logical) =>

let

    //set the radius

    radius = CircleRadius,

    //create a list of numbers from 0 to 359

    anglelist = List.Numbers(0, 359, 1),

    //function to convert degrees to radians

    radians = (a) => (a * 2 * Number.PI)/360,

    //create a list of 360 dates starting from the start date

    unordereddatelist = List.Dates(StartDate, 360,#duration(1,0,0,0)),

    //reverse the list of dates if the Reverse parameter is True

    datelist = if Reverse then List.Reverse(unordereddatelist) else unordereddatelist,

    //generate the list of points on the graph, one for each angle and date

    positionlist = List.Transform(anglelist, each 

                    {_, datelist{_}, Number.Cos(radians(_)) * radius, 

                    Number.Sin(radians(_)) * radius, Date.Month(datelist{_}), 

                    Number.Abs(Number.Cos(radians(_)))*10}),

    //convert the list of points to a table

    outputtable = Table.FromRows(positionlist, {"Angle", "Date", "x", "y", "Colour", "Size"}),

    //set data types

    ChangedType = Table.TransformColumnTypes(outputtable,

                    {{"Angle", type number}, {"Date", type date}, {"x", type number}, 

                     {"y", type number}, {"Colour", type number}, {"Size", type number}})

in

    ChangedType

in

    CircleFunction

 

I then created another Power Query query to call this function 30 times to create 30 circles with different radiuses:

let

    //generate a list of numbers from 0 to 29

    circlelist = {0..29},

    //generate a list of 30 dates starting on 1 January 2014

    datelist = List.Dates(#date(2014,1,1), 30,#duration(1,0,0,0)),

    //call the Circle() function 30 times

    tablelist = List.Transform(circlelist, each Circle(_+5, datelist{_}, Number.Mod(_,2)=0)),

    //combine the resulting tables into a single table

    positionlist = Table.Combine(tablelist)

in

    positionlist

 

And here’s the result of the query plotted on a map using Power Map:

Pretty, isn’t it? You can download the workbook with the Power Query query and the Power Map tour here.

Written by Chris Webb

March 20, 2014 at 11:34 pm

Posted in Power Map, Power Query

One Response

Subscribe to comments with RSS.

  1. […] Pretty Patterns With Power Query And Power Map (Chris Webb) […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,131 other followers

%d bloggers like this: