Importing SVG files from local directory
Well, this post will cover some, maybe not commonly used, techniques that will really enlarge your capabilities to tell a nice story with your data. We will cover the possibility of reading the contents of a local directory, reading data from files, manipulating text and showing and changing SVG files. All within Power BI!
With SVG files (which are vector-based pictures) you can add visuals to your dashboards that are capable of showing the impact of data within pictures that accompany and accomplish the topic of the data, by coloring a part of the SVG. See above how the "globe" and the "flame" get colored, based on the value of a slicer.
In this blog I will go through the techniques I've used to accomplish this, so you can use whatever you need for your own purposes.
Importing SVG files from local directory
This section will lead you through the steps to import SVG files that are stored in a local directory. We will make use of user-defined functions in Power Query, parameters, and text manipulating functions. So open Power Query editor and fasten your seatbelts ;)
Step 1: create a parameter
Firstly, let's create a parameter with the name "svgFolder", type text, and give it the default location of a directory where you want to store the SVG files. Later on, we will use this parameter to tell a query which folder to crawl for SVG images.
Step 2: create user-defined functions
We want to be able to color the SVG's to our liking. That means that we need to strip some of the elements we can't use. We will use two functions to accomplish that.
(1) getSVGHeader:
This function will fetch the header of the SVG file. The header typically looks something like this:
<svg viewBox="0 0 96 96" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" id="Icons_Database" overflow="hidden">Now, create an empty query and pass this underneath code into the "Advanced Editor".
Then, close the editor and rename this newly made function to "getSVGHeader".
So, in short, we will fetch the text from a binary file [Text.FromBinary(File.Contents(mSVG))], and only return the line starting with "<svg" and ending with the first ">". Later on, we will use this function to add this header to a field.
(2) getSVGData:
Now it's time to get the data of the SVG image itself. I won't explain the details here, but just show you a little teaser of how it looks inside an SVG file ;)
<g><ellipse cx="48" cy="18" rx="28" ry="8"/><path d="M68 38C66.8 38 66 37.2 66 36 66 34.8 66.8 34 68 34 69.2 34 70 34.8 70 36 70 37.2 69.2 38 68 38ZM48 30C32.6 30 20 26.4 20 22L20 38C20 42.4 32.6 46 48 46 63.4 46 76 42.4 76 38L76 22C76 26.4 63.4 30 48 30Z"/></g>Again, create an empty query and pass this underneath code into the "Advanced Editor".
Close the editor and rename this newly made function to "getSVGData".
Again, we fetch the text from a binary file [Text.FromBinary(File.Contents(mSVG))], but now we only want the contents that come after the SVG header.
And with "Text.Replace(#"svg_data","fill=", "fillx=")" we get rid of a property that some SVG's have to fill the picture with a color, since we want to color the SVG by ourselves.
In the next step I will show you how we will use these functions to save the SVG header and data into a table.
Step 3: create a table which contains SVG's
Now it finally is the time to create the table that will hold the SVG files that are in the directory listed in your parameter. So, get yourself some SVG files and get started! Ow... make sure these SVG files are not too big... SVG files too big (greater than 32kB?) will break.
Still in "Power Query Editor", (1) create a Blank Query (New Source > Blank Query), (2) rename this query to "_svg files" and (3) c/p the underneath code into the "Advanced Editor".
This code will list all files in the folder that is given by the svgFolder parameter and then invoke both user-defined functions that we created earlier. Again, I won't dive too deep into this code. I guess it's pretty straight forward. Just believe me, it will work ;) but a little explanation may come in handy.
See how we add two columns? One is filled by the "getSVGData function" (getSVGData([Folder Path] & [Name])), the other is filled with the header function (getSVGHeader([Folder Path] & [Name]). [Folder Path] & [Name] are used to tell the functions where the SVG file is, so they can read the contents from the files.
In the end, your table will look something like this. It contains the name, location, create date, etc. And also it contains the header and the data of the SVG's itself.
That's all for now;)
In the coming week (or so), I will add Part II of this journey: how to implement these SVG files in your dashboards. In that topic we will cover techniques like "What-if parameters", "manipulating SVG files", "disconnected slicers", etcetera, etcetera. So, stay tuned....
