# 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:

`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.

### One response

This site uses Akismet to reduce spam. Learn how your comment data is processed.