Adding Sparklines to the Table and Matrix Visual (…


There are 3 features we can put together to make this work:

1. You can add images as column values

2. SVG is a supported image format

3. You can dynamically create an SVG using data points

Turning your data points into SVG data

This is the most complex part, the power query formula to turn data points into SVG data

 

 

let
    Source = (xVals as list, yVals as list) as text => "data:image/svg+xml, ")
in
    Source

 

 

You can enter this in by creating a new blank query, and in the advanced editor, pasting the code here. Rename the query to sparklinesSVG

Using the function.

This function is design to be used on as part of a Group By aggregation. The easiest way to insert it is:

1. In your group by step, add an aggregation with any calculation you want.

2. In the formula bar, replace the aggregation part with the sparklines invocation. E.g.

Before:

 

 

= Table.Group(#"Renamed Columns", {"Geographic Area Name"}, {{"PlaceHolderAggregation", each , each List.Max([Value]), type number}})

 

 

After:

 

 

= Table.Group(#"Renamed Columns", {"Geographic Area Name"}, {{"Sparklines", each sparklinesSVG([XColumn], [YColumn]), type text}})

 

 

In my example, XColumn and YColumn are your X and Y columns. The XColumn and YColumn should not be text, and the XColumn should have their values evenly spread out. Finally, it is important to sort the table by the XColumn ascending ahead of time.

 

If done correctly you should have a column named Sparklines with values that look like:

data:image/svg+xml, http://www.w3.org/2000/svg” height = “100” width=”100″>

 

Making the sparklines appear

1. Add the sparklines column as a value in a table or matrix

2. Change the modeling type of the sparkline to Image URL:

Click the Sparklines column, then under Column Tools, choose Data category as Image URLClick the Sparklines column, then under Column Tools, choose Data category as Image URL

And with that your sparklines should appear:

Table with SparklinesTable with Sparklines



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*