Adding Sparklines to the Table and Matrix Visual (No Custom Visuals)

[artemus]

How does it work

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&colon;image/svg+xml, <svg xmlns=""http://www.w3.org/2000/svg"" height = ""100"" width=""100""><polyline points=""" & 
        (
            let 
                minX = List.Min(xVals),
                maxX = List.Max(xVals),
                minY = List.Min(yVals),
                maxY = List.Max(yVals),
                indexes = {0 .. List.Count(xVals)}
            in
                Text.Combine(List.Transform(List.Select(List.Zip({xVals, yVals, indexes}), each Number.Mod(_{2}, Number.RoundUp(List.Count(xVals) / 20)) = 0 ), each 
                    let
                        x = ((_{0} - minX) / (maxX - minX)) * 100,
                        y = 100 - ((_{1} - minY) / (maxY - minY)) * 100
                    in
                        Number.ToText(Number.Round(x)) & "," & Number.ToText(Number.Round(y))), " ") & """ style=""fill:none;stroke:black;stroke-width:3""/></svg>")
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&colon;image/svg+xml, <svg xmlns="http://www.w3.org/2000/svg" height = "100" width="100"><polyline points="0,100 13,73 20,77 27,68 33,66 40,33 47,16 53,13 60,4 67,3 73,2 80,2 87,1 93,1 100,0" style="fill:none;stroke:black;stroke-width:3"/></svg>

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 URL

And with that your sparklines should appear:

Table with Sparklines

[Martin_Emporium]

Hi @artemus 

Tried this today but unfortunately couldn't get it to work (see my table visual above) ☹️.  The group by formula I used in Customer query was:

= Table.Group(#"Sorted Rows", {"Country"}, {{"Sparklines", each sparklinesSVG([Income], [No. of Cars]), type text}})

I think I followed all the steps outlined and got the svg code seemingly okay.  Any ideas?  "XColumn should have their values evenly spread out" - might this be my issue - was using Adventure Works?

Regards,

Martin

[artemus]

Oh, there seems to be an issue with the form posting.

Replace &colon; with :

[Anonymous]

this is the Most Fantastic  Tip, Worked awesomely