Pretty Patterns With Power Query And Power Map

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:


    //declare function to draw a circle

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


    //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{_}), 


    //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}})






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


    //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)




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.

One thought on “Pretty Patterns With Power Query And Power Map

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 )

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