<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Daniel Marsh-Patrick on Medium]]></title>
        <description><![CDATA[Stories by Daniel Marsh-Patrick on Medium]]></description>
        <link>https://medium.com/@dm-p?source=rss-dcfbf31b00d6------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*V7uaP6qFPvX-s1uks4GUEA.jpeg</url>
            <title>Stories by Daniel Marsh-Patrick on Medium</title>
            <link>https://medium.com/@dm-p?source=rss-dcfbf31b00d6------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 02:48:56 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@dm-p/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[What’s Coming in Violin Plot 1.3.0]]></title>
            <link>https://medium.com/dm-p/violin-130-17e7ba4a6767?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/17e7ba4a6767</guid>
            <category><![CDATA[violin-plot]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[custom-visual]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Fri, 31 May 2019 21:37:16 GMT</pubDate>
            <atom:updated>2019-06-24T19:14:35.852Z</atom:updated>
            <content:encoded><![CDATA[<p>I recently had to release version <a href="https://medium.com/dm-p/violin-120-c726bea99c2b">1.2.0</a> a little early and couldn’t quite cram in everything I wanted. On the plus side it’s now a certified Power BI custom visual, which is a nice personal milestone, and good for anyone who needed the particular bonus functionality that comes with it, as well as assurance that its intentions are legit ;)</p><p>I’ve finished adding in the new stuff, so it’s time for an update! As usual, <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381947">the listing in AppSource</a> is showing the new version number and notes, but it’ll take a couple of weeks for the visuals team to do their final testing and release it proper.</p><p><strong>Update (2019–06–25):</strong> Now live!</p><p>If you are using this through the <a href="https://docs.microsoft.com/en-us/power-bi/power-bi-custom-visuals-organization">organization custom visuals</a> mechanism, you can download the 1.3.0 version of the visual from the <a href="https://bitbucket.org/dm-p/power-bi-visuals-violin-plot/downloads/">repository’s downloads page</a> in the meantime.</p><p>At a high level, the main changes are:</p><ul><li><a href="https://medium.com/p/17e7ba4a6767#0506">Revised Legend</a></li><li><a href="https://medium.com/p/17e7ba4a6767#f72d">Column (Min/Max) Combo Plot</a></li><li><a href="https://medium.com/p/17e7ba4a6767#8466">KDE (Violin) Plot Clamping</a></li><li><a href="https://medium.com/p/17e7ba4a6767#50c9">Y-Axis Start and End</a></li><li><a href="https://medium.com/p/17e7ba4a6767#a414">Barcode Plot Tooltip — Number of Points</a></li><li><a href="https://medium.com/p/17e7ba4a6767#ea85">Violin Stroke Width</a></li></ul><p>Please read on if you wish to delve into the detail a bit more.</p><h3>Revised Legend</h3><p>Another great idea from <a href="https://datasavvy.me/2019/02/14/violin-plots-in-power-bi/">Meagan Longoria’s write up</a> was a legend of her own design, which was a way of providing a quick overview of some of the statistical annotations of the chart (such as mean and median) for the uninitiated. I’ve drawn some inspiration from that and added this into the visual as part of its existing legend functionality:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/430/1*veQ1ExRmJMKKZPgs1dg6CA.png" /><figcaption>Example of updated legend</figcaption></figure><ul><li>Configuration for the new changes can be found in the <em>Legend</em> property menu.</li><li>Categories can be enabled or disabled by toggling the <em>Show Categories</em> property.</li><li>The <em>Show Statistical Indicators</em> property allows you to toggle the display of any indicators such as mean, median and quartiles (plus individual data point if you’ve got the barcode plot enabled).</li><li>If your combo plot shows quartiles, the indicator will be consolidated if they have the same formatting, otherwise you’ll get one for the 1st quartile and another for the 3rd quartile.</li><li>Additionally, the text for any displayed statistical indicators can be customised, just in case the defaults are not suitable for your scenario, e.g.:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/588/1*UtOwTqbhaf_voc39Nr6A0Q.png" /><figcaption>Customising the statistical indicator text (hopefully you’re more creative than I am…)</figcaption></figure><h3>Column (Min/Max) Combo Plot</h3><p>There’s a new combo plot on the block, available from the <em>Plot Type</em> property in the <em>Combo Plot</em> menu:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/159/1*kn0gm0S1_OpgHmjYtUYZuw.png" /><figcaption>New Plot Type option</figcaption></figure><p>The intention of this option is to show the span from minimum to maximum for each category in your visual, similar to a <a href="https://www.anychart.com/chartopedia/chart-type/range-column-chart/">range column chart</a>.</p><p>Here’s an example of what this selecting this <em>Plot Type</em> looks like for the tooth growth dataset:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/472/1*5O670JImpdBM4nAh7yIagw.png" /><figcaption>Column (Min/Max) combo plot default view</figcaption></figure><p>As per the box plot, the default view includes the mean and the median. You can also toggle the display of the quartiles, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/466/1*lovxkEMylLHVjS8yuiiTKQ.png" /><figcaption>Adding quartiles to column combo plot</figcaption></figure><p>Quartiles are configurable in much the same way as for the barcode plot, i.e. you can specify <em>Line Color,</em> <em>Stroke Width</em> and <em>Line Style</em> for each one.</p><h3>KDE Plot Clamping</h3><p><a href="https://community.powerbi.com/t5/Desktop/Violin-Plot-why-does-the-density-plot-extend-past-the-values/td-p/639740">This question</a> in the Power BI Community forums had asked about the run-off or “tail” produced by the KDE plot and how it can extend well beyond normal ranges in some cases. This is a feature of the KDE plot and the parameters used to generate it, such as bandwidth.</p><p>If you wish to limit the range of the KDE plot to the extent of the data, you can now enable the <em>Clamp to Min/Max</em> property in the <em>Violin Options</em> menu, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/947/1*8FJkYgQHLPLtdSUS27gKZw.png" /><figcaption>Example of the same data set, with standard KDE plot (L) and clamped KDE plot (R)</figcaption></figure><p>Please bear in mind that while this may be desirable for your particular use case, sharply cutting the KDE plot when it cannot hit these confines naturally may hide insights about the modality (shape) of your data. As such, it is disabled by default.</p><h3>Y-Axis Start and End</h3><p>To bring the visual in-line with the behaviour of other cartesian charts, the <em>Y-Axis</em> property menu now allows you to specify <em>Start</em> and <em>End</em> values for your chart.</p><p>Behaviour against the KDE plot portion works very much like clamping, detailed above — if your KDE plot exceeds either boundary, the KDE plot will be truncated in a similar way, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/392/1*692OqDhJSKMkELTT2NGRSw.png" /><figcaption>Values in this chart range from 4.2 to 33.9. Here’s what happens if we set the start to <strong><em>10</em></strong> and the end to <strong>30</strong>…</figcaption></figure><p>While this probably goes without saying, note that there is no active filtering, so all statistics and plots will retain the dataset passed to the visual from Power BI and calculate as if the <em>Start</em> and <em>End</em> properties are not set. This means that statistics and KDE plots are inclusive, even if the plotted range may not be fully visible.</p><h3>Barcode Plot Tooltip — Number of Points</h3><p>When using the barcode plot, you might have a large data set with a very low number of distinct values. For example, if we have a 30K dataset with a rating out of 5, starting at 0.5 with intervals of 0.5, then you get a maximum of 10 unique values, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/332/1*y-FFI2ySJ2utGd_63VTNOg.png" /><figcaption>Distribution of unique data points for a 30K row movie ratings dataset</figcaption></figure><p>While we can see the distribution, it might be nice to know how many values there are at each point. The tooltip will now display this as <em># Samples with Highlighted Value </em>for the barcode plot, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/606/1*NHcU5mtO-5b-ICI6QjNySA.png" /><figcaption>The number of samples shown in the default tooltip for the barcode plot</figcaption></figure><p>The formatting of this value is tied to the <em># Samples Display Units</em> in the <em>Default Tooltip Details</em> menu, so both will behave consistently to each other.</p><p>As an aside, datasets like this highlighted potential performance issues, where we would be rendering the line for each data point. In this new version we now only render unique values so for the example above, this is only 10 shapes instead of 30,000. Not all datasets will receive the same benefit, but every little helps!</p><h3>Violin Stroke Width</h3><p>A minor enhancement, but the <em>Stroke Width</em> property in the <em>Violin Options</em> menu can now be set to <strong>0</strong>. This allows you to use the hide the KDE plot line if you just want to work with the <em>Transparency</em> setting in the <em>Data Colors</em> menu, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/764/1*T-_cIAvbj4ZM6VdF4dWYjw.png" /><figcaption>Our typical reference visual for this post. Left has default stroke width (<strong>2</strong>) and the right has stroke width of <strong>0</strong>. Transparency and fill settings are the same for both.</figcaption></figure><p>As always, thanks so much for the feedback and support, and I hope you continue to find the visual useful. Now, time to start work on 1.4!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=17e7ba4a6767" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/violin-130-17e7ba4a6767">What’s Coming in Violin Plot 1.3.0</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What’s Coming in Violin Plot 1.2.0]]></title>
            <link>https://medium.com/dm-p/violin-120-c726bea99c2b?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/c726bea99c2b</guid>
            <category><![CDATA[custom-visualization]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Wed, 20 Mar 2019 18:34:01 GMT</pubDate>
            <atom:updated>2019-04-10T19:53:24.794Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YLBd2J0iisSKKT8i" /><figcaption>Photo by <a href="https://unsplash.com/@zldoty?utm_source=medium&amp;utm_medium=referral">Zach Doty</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>It’s time for another update to the <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381947">Violin Plot custom visual for Power BI</a>! The visual has been updated in AppSource but will take up to a couple of weeks for Microsoft to do their compatibility testing and for the updated visual to appear in your reports.</p><blockquote><strong>2019/04/11</strong>: this is now live</blockquote><p>In the meantime, here’s a run-down of the stuff you might be interested in for when this does go live:</p><h3>Bug Fixes</h3><p><a href="https://twitter.com/FrankTonsen">Frank Thonsen</a> reported a particularly nasty problem with zeros getting treated as NULL in the visual and its aggregations in some cases. Due to this issue I’ve opted to release earlier than intended, so some features have been bumped at the cost of improved data quality. Apologies if you’ve been affected by this particular issue.</p><h3><em>Show Data</em> Hotkey Support</h3><p><a href="https://twitter.com/MMarie">Meagan Longoria</a> recently provided <a href="https://datasavvy.me/2019/02/14/violin-plots-in-power-bi/">a very nice write-up of the visual</a>, and made several great suggestions for improving things. One of these is enabling the Alt + Shift + F11 shortcut to trigger the <em>Show data</em> menu option and this has been added.</p><h3>Improved Tooltip Support</h3><p>Power BI Desktop (March 2019) <a href="https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-2019-feature-summary/#tooltipFormatting">introduced improved tooltip formatting options</a>. We recently saw the <a href="https://docs.microsoft.com/en-us/power-bi/desktop-tooltips">introduction of report page tooltips as well</a>. Changes have been applied to the visual to support these features and make tooltip formatting consistent with core visuals.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/513/1*od--cMa97PKzBZuY7zH0Sg.png" /><figcaption>We need to go deeper…</figcaption></figure><p><strong>This has resulted in some minor changes for the end-user</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/190/1*xd8W6Bi3IN0q6CTsOiUAuw.png" /><figcaption>The core Tooltip menu is in!</figcaption></figure><ul><li>The <em>Tooltip</em> menu now contains all core tooltip formatting options, i.e. it’s the standard Power BI <em>Tooltip</em> menu that you see in the core visuals.</li><li>You can specify a report page tooltip to be used by the visual — providing your measure is set up with one — or use the default tooltip.</li><li>Options for configuring specific data points and aggregates have been retained, but the menu is now named <em>Default Tooltip Details</em>. Under the hood, this is the same set of properties, so anything you’ve configured in any of your visuals to date will carry over to the new version.</li></ul><h4>Why two Different Menus?</h4><p>Power BI custom visuals can’t access the property states of the inbuilt menus as they aren’t provided to any routines. While we can add custom properties to these menus, it’s difficult to control their behaviour based on the standard properties.</p><p>For instance, switching the Tooltip toggle to OFF will grey out the core properties but not the custom ones we’ve introduced. Therefore, I’ve kept the toggles and default tooltip-specific configuration in this separate property menu.</p><h3>Culture-Awareness &amp; Formatting for Measures</h3><p>The visual was previously coded to only support the en-US locale, meaning that if you had applied locale-specific measures to your visuals then this would not be handled as intended. We were also limited to sharing the measure format from the data model between the Y-axis and the tooltip, which was not always ideal. Features to improve these were on the roadmap but got brought forward a bit due to feedback (thanks again to both Meagan and Frank for flagging).</p><p>This update will yield measure formatting to the Power BI locale you’re using as well as your data model, meaning that locale-specific formatting should be observed, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/435/1*UHrToWoAby-j65pLy5js0A.png" /><figcaption>The same plot, for <strong>en-US</strong> (English, left) and <strong>de</strong> (Deutsch, right) locales</figcaption></figure><p>These changes will also be present in the default tooltip.</p><p>There may well be situations where you want to display different levels of precision to your tooltip values (for example, formatting the Y-axis to display whole numbers, but specifying decimal places in the tooltip for the values surrounding measures.</p><p>The <em>Default Tooltip Details</em> menu now has the following additional properties:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/182/1*j57RgJLBnj0c6qNDwp6XXQ.png" /><figcaption>New <strong>Default Tooltip Details</strong> menu formatting options</figcaption></figure><ul><li>The <strong># Samples</strong> properties allow you to format the number used to display this particular data point (particularly useful if this is in the thousands but your measure values are not).</li><li>The <strong>Measure</strong> display units will apply the specified units &amp; decimal places to the remaining values in the tooltip.</li></ul><p>Here’s an example of everything configured a bit differently, just for visual confirmation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/462/1*-gKRts1jmz8AcXfyBLgRaQ.png" /><figcaption>Different formatting configuration for Y-axis, # Samples (thousands) and other tooltip values (4 decimal places)</figcaption></figure><p>Thanks for your continued feedback, and I hope that these additional features and fixes continue to give you a lovely violin-y experience :)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c726bea99c2b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/violin-120-c726bea99c2b">What’s Coming in Violin Plot 1.2.0</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What’s Coming in Violin Plot 1.1.0]]></title>
            <link>https://medium.com/dm-p/whats-coming-in-violin-plot-1-1-0-236f3d12a8e0?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/236f3d12a8e0</guid>
            <category><![CDATA[custom-visual]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Mon, 14 Jan 2019 21:02:31 GMT</pubDate>
            <atom:updated>2019-01-14T21:02:31.742Z</atom:updated>
            <content:encoded><![CDATA[<p>I’ve been pretty happy with how the development of the <a href="https://medium.com/@run_dmp/gratuitous-dax-and-senseless-violins-9485519fb0ec">violin plot custom visual for Power BI</a> has gone, so I’ve been chipping away at my wish list of additional features in order to improve it further. These are available in version 1.1.0 of the visual.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3MWh1Xlw-5ISwyq2" /><figcaption>Photo by <a href="https://unsplash.com/@josepmolina?utm_source=medium&amp;utm_medium=referral">Josep Molina Secall</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>The <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381947">visual listing has already been updated</a> but there’s potentially a couple of weeks before Microsoft complete compatibility testing and you’ll be able to load the new version in your reports.</p><p>As such, I can’t update the sample workbook just yet as it uses the same mechanism, so thought the following may whet your appetite in the meantime if you’re a user of the visual.</p><h3>KDE Bandwidth Changes</h3><p>In the 1.0.0 release, the visual would apply a uniform bandwidth to all violins within the data set, whether this was manually specificed in the properties, or estimated using the rule-of-thumb appropriate for the selected kernel.</p><p>A new <strong>Bandwidth By Category</strong> property is now present in the <strong>Violin </strong>menu, which when enabled will allow the new behaviour. This may assist with finding a better representation of distribution if your individuals categories might have their own unique characteristics.</p><h4>Specify Bandwidth = Off (Rule-of-Thumb Estimation)</h4><p>When the visual estimates the bandwidth rather than being manually specified, the rule-of-thumb bandwidth will be calcluated over the data points in each category, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/783/1*9uPSqe7U-qZjYuQe-tIWIA.png" /><figcaption>Differences between uniform estimation of bandwidth and estimation by category. The left plot has a bandwidth of <strong>7.91</strong>, whereas the right plot has values of <strong>4.50</strong>, <strong>4.42</strong> and <strong>3.77</strong> respectively.</figcaption></figure><h4>Specify Bandwidth = On (Manually Provided Values)</h4><p>If you opt to specify a bandwidth value manually, the behaviour is a little different.</p><p>As before, the <strong>Bandwidth</strong> field is present, but serves as a default for the individual category values that will appear after enabling <strong>Bandwidth By Category, </strong>e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/561/1*FE5O9SbIQ8Lp1YgKhF_oCQ.png" /><figcaption>Defaults when <strong>Specify Bandwidth</strong> and <strong>Bandwidth By Category</strong> are enabled.</figcaption></figure><p>If we change the value for the <em>1</em> category to <strong>3</strong>, this will only update that category, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/567/1*A6XB5Q1hX2U3lS0f2WcEAw.png" /><figcaption>After applying a bandwidth value to an individual category. The <strong>0.5</strong> and <strong>2</strong> categories remain unchanged.</figcaption></figure><p>If we now change the <strong>Bandwidth</strong> value to <strong>5</strong>, this will update the other two categories that we haven’t yet set with a new default and leave the one we specified unchanged, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/567/1*2zKO4CyLSTMaTmtnRTkJLQ.png" /><figcaption><strong><em>0.5</em></strong> and <strong>2</strong> categories updated with new default value of <strong>5</strong> from the <strong>Bandwidth</strong> field above.</figcaption></figure><p>The default will also be applied to any new categories that might appear in your data after a refresh or a slicer value is reverted.</p><p>If you’ve changed a category to a different value to the default, you can ‘revert’ it to the default by deleting the value so that the field is blank. It will then replace itself with the default value.</p><h3>Data Distribution Plot Changes</h3><h4>Box Plot &gt; Combo Plot</h4><p>The visual originally contained a box plot as an accompaniment to the KDE plot (or violin). This is typical of these charts, but there’s other ways to visualise distribution (more on that shortly).</p><p>As such, the <strong>Box Plot</strong> menu has now changed to <strong>Combo Plot</strong>. Within this menu, there’s now an option to choose your <strong>Plot Type</strong>, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/245/1*HErCCkkro0qUERIdcLvwqQ.png" /><figcaption>Renamed <strong>Combo Plot</strong> menu, showing new <strong>Plot Type</strong> property.</figcaption></figure><p>For compatibility with previous versions, this is set to <em>Box</em> by default, so anything you’ve done thus far will very much remain as-is.</p><h4>Barcode Plot</h4><p>In addition to the above, the option to choose a <em>Barcode</em> plot is available in the <em>Plot Type</em> combo box. This will render your indvidual data points as a barcode (or strip) plot within the violin and lets you see their placement within the violin, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/384/1*7H-sb3VpIjBTX9z28Zv7-A.png" /><figcaption>Changing the <strong>Combo Plot</strong> to <strong>Barcode</strong>.</figcaption></figure><p>To help with exploring this a bit more, you can mouse over the barcode to emphasise the nearest data point and get its value in the tooltip, as well as the other information that’s available with the box plot (plus all other options within the <em>Tooltip</em> menu), e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/274/1*Bem2eQSy0-0QGIwh8rY_ug.png" /><figcaption>Highlighting a data point in a barcode plot. The value is at the bottom.</figcaption></figure><p>The barcode plot will expose the following properties for you to be able to configure it with:</p><ul><li><strong>Inner Padding (%) </strong>— adjusts the width of the bars relative to the violin plot.</li><li><strong>Stroke Width </strong>— changes the stroke width of the data point bars.</li><li><strong>Bar Color </strong>— changes the colour of the data point bars.</li><li><strong>Show 1st &amp; 3rd Quartiles </strong>— if enabled, this will plot the quartiles as lines (similar to the mean). Each line has its own configurable <em>Line Color</em>, <em>Stroke Width</em> and <em>Line Style</em></li><li><strong>Show Median (Line)</strong> — as per the <em>Box</em> combo plot option. This update adds <em>Line Color</em>, <em>Stroke Width</em> and <em>Line Style</em> properties and these also apply to the <em>Box</em> option, meaning that your boxplot stroke width and median stroke width can now be independent of each other.</li></ul><h4>Box Plot</h4><p>To accomodate the <em>Barcode</em> plot, the <em>Box</em> plot properties have also been enhanced as a result:</p><ul><li>Median Line options have been increased as per the above list for the barcode plot</li><li>The Mean (circle) also now has independently configurable <em>Stroke Width</em>.</li></ul><p>Thanks very much to those who have been downloading and using the visual, and for the kind feedback and suggestions. It’s a lot of fun to work on and I’m really pleased that people are finding it useful!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=236f3d12a8e0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/whats-coming-in-violin-plot-1-1-0-236f3d12a8e0">What’s Coming in Violin Plot 1.1.0</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Generating Rows by Month for Date Ranges in Power Query]]></title>
            <link>https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/9baf62ed8e99</guid>
            <category><![CDATA[power-query]]></category>
            <category><![CDATA[data-transformation]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Mon, 14 Jan 2019 01:55:22 GMT</pubDate>
            <atom:updated>2019-01-14T01:55:22.392Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>I’ve recently procured a data set that summarises company statistics at a financial year level.</p><p>I’d like to do some like-for-like analysis against these companies over time using additional time-based data, but they all operate with different financial year start and end dates.</p><p>This is somewhat challenging when the drift between them can be 11 months, purely based on when they choose to report their progress, and it’s just not straightforward to just use their declared financial year as a data point that can sensibly compare them.</p><p>A lot can happen in that time, but I <em>can</em> break each year out into a number of rows representing a given month, and this will give me a better level of granularity. Due to the time between annual reports, I also have some half-yearly data for those that haven’t done a full year report in a while, so I now have a 6 month window to work with in some cases, too. Fortunately a large number of my statistics can assume an even split per month.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/490/1*2uopoNVzNqykS7g6pwMRSw.png" /><figcaption>Something like this…</figcaption></figure><p>My usual model for this is writing some SQL in my data warehouse, joining my date dimension on the start and end dates for the year, and grabbing out the records that correpsond to month end (effectively giving me an additional 12 rows for each row I had originally).</p><h3>On to the Actual Problem</h3><p>The data I have is sourced externally and not in a state I’d like to include in a database while I’m experimenting with it, so thought I’d have a go at this in Power Query.</p><p>The first hurdle I fell at was that Power Query doesn’t have a join (or <strong>Merge</strong> as it’s known in there) that will work in the same way a BETWEEN clause would work in SQL. A quick search led me to <a href="http://radacad.com/dates-between-merge-join-in-power-query">another great post by Reza</a>, who of course, has already tackled something similar! However this works at the day-level, and Power Query has pretty good support for this — months… not so much.</p><p>Fortunately, we can still manage this with some M formulas rather than built-in transformations. At a high-level, here’s what we’re aiming to do:</p><ol><li>Calculate span between start and end dates in months</li><li>Create a row for each full month between these two dates</li><li>Calculate the ‘month end’ date for each intervening row</li></ol><p>Beyond this, I’d do things such as calculating spread of annual totals with DAX, as some more complicated modelling may be required and Power Query probably isn’t the best place to do this, particularly if there’s a dependency on other elements of the data model.</p><h3>Let’s Get Solving</h3><p>Once I’ve loaded my data, I’ll have something that looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/948/1*7ydgrUC7GtqpLiN_OaDsWg.png" /><figcaption>Mocked-up data for 4 different companies, with 4 different finanical year start/end dates, after loading source data from Excel into Power Query</figcaption></figure><h4>Month Span Calculation</h4><p>To calculate the <strong>Month Span</strong>, click <strong>Add Column</strong> &gt; <strong>Custom Column</strong>. In the dialog box, we’ll add our formula, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*0oxiWKhmpkAlMbQLDN4QYg.png" /><figcaption>Month span calculation, based on <a href="https://community.powerbi.com/t5/Developer/Number-of-Months-between-two-dates/m-p/90008/highlight/true#M3196">this post in the Power BI forums </a>— I’ve added 1 to the total to give me a denominator</figcaption></figure><p>For your copy/paste convenience, here’s the raw code:</p><pre>  (12 * (Date.Year([FY End]) - Date.Year([FY Start])))<br>+ (Date.Month([FY End]) - Date.Month([FY Start]))<br>+ (if Date.Day([FY End]) &lt; Date.Day([FY Start]) <br>    then -1 <br>    else 0<br>  )<br>+ 1</pre><h4>Generating Rows</h4><p>Now we have the number of months, we can create the requisite number of rows. We do this by generating a list that we can subsequently expand.</p><p>Click <strong>Add Column</strong> &gt; <strong>Custom Column</strong> and we’ll add our formula, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*ZoSlM91XIrMvdIISsdGrTQ.png" /><figcaption>M code for generating a 1..N list for the number of months in our start/end date span</figcaption></figure><p>Raw code:</p><pre>List.Numbers(<br>  1,<br>  [Month Span]<br>)</pre><p>At this point, we’ll now have a column containing a List entry. Click the expand button at the top of the <strong>Month List</strong> column and then <strong>Expand to New Rows</strong>, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/741/1*E31PnwH8OdjH90jCvMlXvA.png" /><figcaption>Expanding our list into rows</figcaption></figure><p>You’ll now have a <strong>Month List</strong> column with as many rows as the <strong>Month Span</strong> column indicates for that particular record. This will run from 1 to N (working like an index column within each particular company financial year).</p><h4>Month End Date</h4><p>I do this primarily so that everything snaps to the same place (month start might be easier, but if I’m getting additional structured metrics I want them as at the end of the month in the report I’m working on).</p><p>We can do this by adding another custom column, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*qS8nZxx14Ht03Kksx_5tYw.png" /><figcaption>M code to use the index column to resolve month end date</figcaption></figure><p>Raw code:</p><pre>Date.EndOfMonth(<br>  Date.AddMonths(<br>    [FY End],<br>    0 - [Month Span] + [Month List]<br>  )<br>)</pre><p>This fulfils the objectives above, and we now have one row per month per financial year per company, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gtiEiRxdaQQ-x3jeLpgUqg.png" /><figcaption>Our complete recipe</figcaption></figure><h3>Wrapping Up</h3><p>As mentioned earlier on, there’s a need for me to do some kind of spread calculation and other things that DAX may be more useful for, depending on contextual stuff that I’m yet to explore, but for now I at least have the grain I need to work with. Now, I can get on with my analysis!</p><p>If you’ve got this far, thanks very much for reading, and hopefully this might have helped you out a little bit.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9baf62ed8e99" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99">Generating Rows by Month for Date Ranges in Power Query</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Gratuitous DAX and Senseless Violins]]></title>
            <link>https://medium.com/dm-p/gratuitous-dax-and-senseless-violins-9485519fb0ec?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/9485519fb0ec</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[statistics]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Thu, 13 Dec 2018 00:42:31 GMT</pubDate>
            <atom:updated>2018-12-13T01:46:50.975Z</atom:updated>
            <content:encoded><![CDATA[<p>There’s an <a href="https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals">ever-growing pool</a> of really useful custom visuals for Power BI, and I’ve been <a href="https://medium.com/@run_dmp/making-small-multiple-line-charts-in-power-bi-fc3515d5396f">really excited to contribute to this recently</a>.</p><p>Even after a few years, there are still some gaps and I’ve been looking to expand my knowledge with finding and developing high-utility charts that might still be needed. They take a while, so I’m not exaclty trucking them out, but it is time for a new challenger to enter the arena.</p><h3>Enter the Violin Plot</h3><p>For the uninitiated, if you want to understand more about the composition of your data you might have used a <a href="https://en.wikipedia.org/wiki/Box_plot">box plot</a> to do this. They’re very handy, and whilst they show the distribution of your data through quartiles , they don’t show the shape of this distribution and you would need to do further analysis elsewhere.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/464/1*hh_yD_jeOx3-ED9GgT61Sw.png" /><figcaption>Example violin plot using custom visual</figcaption></figure><p>A <a href="https://en.wikipedia.org/wiki/Violin_plot">violin plot</a> is essentially a way of achieving this patricular goal.</p><p>It’s a combination of a box plot, and a sideways <a href="https://en.wikipedia.org/wiki/Kernel_density_estimation">kernel density estimation</a> (KDE) plot, which, if you want an ELI5-style answer (and that 5 year old has done basic statistics), are analagous to a “smooth <a href="https://en.wikipedia.org/wiki/Histogram">histogram</a>”.</p><p>They can sometimes cause an <a href="https://xkcd.com/1967/">initial chuckle or two</a> (or seemingly never-ending if you’re Mrs. M-P), but they can be pretty handy for these distribution and statistical analysis use cases.</p><p>I’ve attempted to produce a violin plot that is pretty good for casual use but also allows some fine-tuning for those cases where data is… well, data, or for those who want to see how these variables can effect the plot and possibly (hopefully) provide more meaningful insights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/363/1*rcU1GHfaTHPdauKfu38kvA.png" /><figcaption>You can split by category too!</figcaption></figure><p>This wouldn’t have been possible without some really great work by Mike Bostock on how to <a href="https://bl.ocks.org/mbostock/4341954">plot KDE using d3.js</a> (and d3.js of course), as well as Andrew Sielen for <a href="http://bl.ocks.org/asielen/92929960988a8935d907e39e60ea8417">showing it can be done</a>.</p><h3>Tuning Your Violin</h3><p>There’s a few factors than can have an effect on how this plot looks:</p><h4>Kernel Function Applied</h4><p>The <a href="https://en.wikipedia.org/wiki/Kernel_(statistics)#Kernel_functions_in_common_use">kernel function</a> is a particular algorithm that is used for the smoothing calculations. The visual contains the following kernels:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/695/1*46I5PksAUhlXjFGPh_rV_A.png" /><figcaption>Kernel functions available. I promise the differences can be more significant, depending on your data ;)</figcaption></figure><h4><strong>Kernel Bandwidth</strong></h4><p>The bandwidth is a parameter that affects the smoothing of the plot. Too low and it potentially identifies too many features; too high and it will smooth too much and miss some out.</p><p>By default, the violin plot visual will apply a <a href="https://en.wikipedia.org/wiki/Kernel_density_estimation#A_rule-of-thumb_bandwidth_estimator">rule-of-thumb calculation</a> to derive a suitable bandwidth. This can work well for normal distributions but your mileage my vary for multimodal or skewed distributions. Fortunately you can opt to override this with a manual amount, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/523/1*ZSYskFC7zG-TcyPArwz3XA.png" /><figcaption>How bandwidth can potentially affect a violin plot for the same data</figcaption></figure><p>The visual has a tooltip property menu, which allows you to specify a number of statistics for inclusion. The KDE bandwidth is not on by default, but you can enable this if you want to know what the visual is estimating. If manually specifying the bandwidth then the estimated will be included for info but shows as <em>N/A</em>, just in case you wish to compare these two values.</p><h4>Sampling Resolution</h4><p>Sampling resolution is analagous to the number of bins you might assign a histogram. Because everyone’s axes can be different we attempt to make this a bit more user friendly with three settings, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/502/1*M1nbbbs37ypszL4Hyv-i_A.png" /><figcaption>Examples of resolution differences between violin plots</figcaption></figure><p>Similar to bandwidth, the resolution may have an effect on the features displayed in your data. If more features are displayed than desired, even at standard resolutions, you can offset this by increasing the bandwidth.</p><h3>I Want It!</h3><p>Good! <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381947?src=website&amp;mktcmpid=blogpost_001">You can get this visual from AppSource</a> or using the Power BI visuals marketplace within the application. There’s a sample workbook available to showcase some of the features.</p><p>The visual is also <a href="https://bitbucket.org/dm-p/power-bi-visuals-violin-plot">open source</a>, if you want to have a look around.</p><p>I hope you find the visual useful, and if you have any feature requests, or issues to report, please head over to the support page for more details. While this is product of my free time, I’ll do my best to keep it truckin’.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9485519fb0ec" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/gratuitous-dax-and-senseless-violins-9485519fb0ec">Gratuitous DAX and Senseless Violins</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Making Small Multiple Line Charts in Power BI]]></title>
            <link>https://medium.com/dm-p/making-small-multiple-line-charts-in-power-bi-fc3515d5396f?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/fc3515d5396f</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Sat, 14 Jul 2018 02:32:21 GMT</pubDate>
            <atom:updated>2018-07-23T19:40:15.467Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p><em>I’ve recently found myself pining for small multiples as a feature in Power BI. Ended up doing it myself (for line charts, at least). Was productive.</em></p><h3>History Lesson</h3><p>Back in the day when our original Power BI stack was Power Query, PowerPivot and Power View inside Excel, we had this handy little feature in the Power View properties pane called <strong>Vertical Multiples</strong>, which would just take that category and create as many charts as you needed, based on the values contained in that field. I could do something like this pretty easily:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/597/1*VVA4iF7Z2NfTJ8qFjhSNdQ.png" /><figcaption>Example of a small multiple chart using Excel Power View — image courtesy of Microsoft Support</figcaption></figure><p>This still isn’t part of the v2 product, and I’ve been wanting to do this with a line chart for a couple of use cases I’ve had hanging around the office.</p><h3>Finding Something… Anything…</h3><p>There’s some visuals out that offer small multiples in some capacity, such as the <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380898?tab=Overview">infographic designer</a>… but it’s a little buggy with negative values.</p><p>There’s some pretty good workarounds online for making small multiple bar charts using the matrix visual and conditional formatting (<a href="http://danscorner4businessintel.blogspot.com/2017/07/small-multiples-in-power-bi.html">1</a>) (<a href="http://www.decisivedata.net/blog/small-multiples-trellis-bar-charts-in-power-bi">2</a>), but I can’t extend this to my use case.</p><p>I briefly got into using the R visuals features for a while, and it was real easy to use ggplot to knock something up. I didn’t find this responsive enough or configurable enough for my needs without significant work. I finally took the plunge and started to research custom visuals development — I’ve used d3.js before, so it can’t be that hard… right?</p><h3>My First Custom Visual</h3><p>Turns out… it isn’t too bad! After disappearing down respective Typescript and Power BI custom visuals API rabbit holes for way longer that was probably healthy, I have emerged with something that works pretty well for me. Here’s a couple of examples:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3UGKRxI5U8YqYhYtdF0zZg.png" /><figcaption>Small multiple “seasonality” line chart using a custom visual</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ocRZC3vzyKMBNcjDH5GCIQ.png" /><figcaption>Multiple measures and rows</figcaption></figure><h3>Maybe You’re Interested in Using It Too?</h3><p>I’ve tinkered enough with it to be happy to release it for other small multiple cravers — it’s currently going through the AppSource submission process and should be available soon through the regular Power BI custom visuals marketplace.</p><blockquote>Update (2018–07–24): it’s now <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381711?src=website&amp;mktcmpid=blogpost_001">available in AppSource</a></blockquote><p>But… if you want it ahead of then, you can download version 1.0.0 from here:</p><ul><li><a href="https://bitbucket.org/dm-p/power-bi-visuals-small-multiple-line-chart/downloads/smallMultipleLineChart.1.0.0.pbiviz">Custom visual file</a></li><li><a href="https://bitbucket.org/dm-p/power-bi-visuals-small-multiple-line-chart/downloads/smallMultipleLineChart.1.0.0.pbix">Sample workbook</a></li></ul><p>I don’t want to repeat too much of the usage info in that file in this article, but if you want to read some further details online, I’ve added a <a href="https://bitbucket.org/dm-p/power-bi-visuals-small-multiple-line-chart/wiki/browse/">documentation wiki to my BitBucket repository</a>.</p><p>I’m intending to keep pushing on with developing this further and get it certified for use in the Power BI service. There’s a lot more to do… and learn… and fix. I’ll keep you posted on progress as we go!</p><p>Feel free to use the <a href="https://bitbucket.org/dm-p/power-bi-visuals-small-multiple-line-chart/issues?status=new&amp;status=open">issue tracker</a> to log any issues or ideas and I’ll do my best to work on them as time allows. I’ve also added a <a href="https://bitbucket.org/dm-p/power-bi-visuals-small-multiple-line-chart/wiki/Release%20Notes%20and%20Known%20Issues">list of known issues to the wiki, as well as a high-level roadmap</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fc3515d5396f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/making-small-multiple-line-charts-in-power-bi-fc3515d5396f">Making Small Multiple Line Charts in Power BI</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Implementing Soundex in Power Query (M)]]></title>
            <link>https://medium.com/dm-p/implementing-soundex-in-power-query-m-71169e0bd24f?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/71169e0bd24f</guid>
            <category><![CDATA[sms]]></category>
            <category><![CDATA[soundex]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[power-query]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Wed, 16 Aug 2017 03:13:26 GMT</pubDate>
            <atom:updated>2018-04-12T03:04:36.808Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>I’ve recently been working with some data in Power BI containing contact names and looking into how I might be able to clean it up and reduce issues with typos etc., or attempt fuzzy joining data. I’ve looked at similar challenges in T-SQL before using the <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql">SOUNDEX</a> function to give me a place to start in this area and was hoping that Power Query had something similar… unfortunately not.</p><p>Fortunately, the <a href="https://msdn.microsoft.com/en-us/library/mt211003.aspx">M language</a> that sits behind Power Query is quite extensive and I figured I could probably implement this in a <a href="https://blogs.msdn.microsoft.com/mvpawardprogram/2013/08/19/creating-power-query-functions/">function that could be called from my transformation</a>.</p><p><em>(It had occurred to me that I could probably use an R transformation for this but from a personal perspective, it’s a welcome opportunity to see a bit more of what the M language is capable of)</em></p><h3>The Soundex I’m Looking For…</h3><p>The algorithm I’m implementing is the <a href="https://en.wikipedia.org/wiki/Soundex#American_Soundex">American Soundex</a>, but this code could be tweaked for the other types if you want to mix things up a bit.</p><p>I’ve created some simple people data from <a href="https://www.mockaroo.com/">Mockaroo</a> and here’s a small sample:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*ZW4H_qGJmliw9X1DKf31NA.png" /><figcaption>Some of our sample data</figcaption></figure><p>If I choose a few othese last names from the above table, based on the algorithm, my expected output would be:</p><ul><li><strong>Paynton</strong> =&gt; P535</li><li><strong>Epple</strong> =&gt; E140</li><li><strong>Bremen</strong> =&gt; B655</li></ul><h3>The Finished Product</h3><p>If you just want to get on with using it then you can follow the instructions below to get started. I have commented and whitespaced the code fairly heavily but if you want to learn more about how it works then I have written a detailed analysis after this section.</p><blockquote><strong>Disclaimer:</strong> the code hasn’t been performance tested or tuned and might run quite terribly with a lot of rows. I’d like to optimise this further and I think that there’s a lot of low hanging fruit in there if I ever get to it…</blockquote><blockquote><strong>2018/04/12</strong>: <a href="https://medium.com/@run_dmp/thanks-i-personally-havent-spent-much-more-time-on-it-since-as-my-use-case-has-dwindled-away-a4dc702dfddc">there’s a slightly better version in the comments below</a>.</blockquote><ol><li>Create a blank query (in Power Query or Excel) — the instructions assume you’ve named the query fn_Soundex but you can rename accordingly if it doesn’t suit your personal preference.</li><li>Open up the Advanced Editor and paste in the following code:</li></ol><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9868549d3da90ee66bde8faa052462cb/href">https://medium.com/media/9868549d3da90ee66bde8faa052462cb/href</a></iframe><p>Once you click <strong>Done</strong>, you should get something like the following in your Power Query editor:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j9Do9oIdNIW_kU6th0iouw.png" /><figcaption>fn_Soundex added to Power Query</figcaption></figure><p>I can then add this to my <strong>People </strong>query<strong>:</strong></p><ol><li>Under <strong>Add Column</strong>, click <strong>Invoke Custom Function</strong>.</li><li>Fill out the dialog with the necessary parameters and click <strong>OK</strong>, e.g.:</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*77w2OReT82c1o0Bbr2YJuw.png" /><figcaption>Adding the function column to soundex our <em>Last Name</em> field</figcaption></figure><p>You’ll now see a new column for your soundex result, e.g.:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/524/1*5BwpsOpaf4zlCE7ITAcH3A.png" /><figcaption>Soundex calculated!</figcaption></figure><p>Doing a quick spot-check against our earlier choices shows that we’re doing this right. I have also done a lot more testing against a more extensive list of names and these all look good too!</p><h3>Breaking it Down</h3><p>This section helps to decompose the approach taken to show how some of the algorithm has been solved using the M language. We’ll work with the <strong>Paynton</strong> surname as our example and step through the code and look at the output.</p><h4>Cleaning the Input</h4><p>First things first, we need to do a couple of things to our input:</p><ol><li>Get the text down to alpha characters only (a through z). Even though the algorithm throws a lot of characters out, if the first character is a vowel or an excluded consonant then we still need to use it for our generated code.</li><li>As M is case-sensitive, convert the string to upper-case.</li></ol><p>We can do this in one go by converting each character to its numeric representation and surrounding it with the case conversion:</p><pre>#&quot;Cleaned String&quot; = <br>    Text.Upper(<br>        Text.Remove(<br>            string, <br>                List.Transform(<br>                    {0..64, 91..96, 123..50000}, <br>                    each Character.FromNumber(_)<br>                )<br>            )<br>    )</pre><p>Submitting our data will simply result in an upper-case version of the string we submitted which is ideal, but if our name contains whitespace, hyphens or some other non-alpha character for whatever reason, then it will strip it out.</p><h4>Grabbing the First Character</h4><p>Our generated soundex will always want the first alpha character, so this is simply getting the first character of the cleansed input:</p><pre>#&quot;First Character&quot; = Text.Start(#&quot;Cleaned String&quot;, 1),</pre><p>For our example, we’ll simply end up with P here.</p><h4>Creating our Working Data for the Digits</h4><p>So far, so straighforward. The next set of steps gives us our working data for the generation of the 3-digit portion of the generated code.</p><p>In some other language I might have solved this with a simple loop and counter to allow me to iterate over the remaining characters, checking and mapping accordingly, and then checking the previous characters for consecutive exclusions. I could apply a set-based approach here to be in-keeping with M’s modus operandi:</p><ol><li>Create a table with single column; each row representing each individual character.</li><li>Add an index column, starting at 0 and incrementing by 1, which is a proxy for a loop counter.</li><li>Add an empty column to hold our mapped character.</li></ol><pre>#&quot;Split Character List&quot; =<br>  Table.FromList(<br>    Text.ToList(Text.Range(#&quot;Cleaned String&quot;, 1)),<br>    Splitter.SplitByNothing(),<br>    {&quot;Character&quot;}<br>  ),<br>#&quot;Character Index Column&quot; = <br>  Table.AddIndexColumn(<br>    #&quot;Split Character List&quot;,<br>    &quot;Index&quot;, <br>    0, <br>    1<br>  ),<br>#&quot;Soundex Code Column&quot; = <br>  Table.AddColumn(<br>    #&quot;Character Index Column&quot;, <br>    &quot;Soundex&quot;, <br>    each null,<br>    type text<br>  )</pre><p>After this stage, we have the following table established:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*5DcTPaxXNaN60qCRFuiQAw.png" /><figcaption>Working table for remaining characters, with index and column to store mapping</figcaption></figure><h4>Processing the Table and Mapping Values</h4><p>The largest portion of the code is concerned with applying the mappings to the data. The basic idea here is that we look at each row and populate the <strong>Soundex</strong> column accordingly:</p><ul><li>If the character is a vowel or excluded consonant, then set it to null.</li><li>If the character is usually permitted but the subsequent character is a repetition, then set this to null also.</li></ul><p>I’d considered filtering excluded characters first but this way, if i want to tweak my algorithm to handle other characters differently, then I have the capacity to do so here without too much refactoring:</p><pre>#&quot;Processed Characters&quot; = Table.FromRecords(<br>  Table.TransformRows(<br>    #&quot;Soundex Code Column&quot;,<br>    each<br>      Record.TransformFields(<br>        _,<br>        {&quot;Soundex&quot;, (f)=&gt;<br>          let <br>            /*  Set flag to true if consecutive characters found */<br>              omitChar = (<br>                if [Index] &lt;&gt; 0<br>                  and #&quot;Soundex Code Column&quot;<br>                    {[Index] - 1}[Character] = [Character]<br>                  then true<br>                else false<br>              ),<br>            /*  Check for character in list and assign a numeric      <br>                value, if not eligible for omission */<br>                soundexChar = (<br>                  if omitChar = false<br>                    then<br>                      if      List.Contains(<br>                                {&quot;B&quot;,&quot;P&quot;,&quot;F&quot;,&quot;V&quot;},<br>                                [Character]<br>                              ) <br>                              then &quot;1&quot;<br>                      else if List.Contains(<br>                                {&quot;C&quot;,&quot;G&quot;,&quot;J&quot;,&quot;K&quot;,&quot;Q&quot;,&quot;S&quot;,&quot;X&quot;,&quot;Z&quot;},<br>                                [Character]<br>                              )<br>                              then &quot;2&quot;<br>                      else if List.Contains(<br>                                {&quot;D&quot;,&quot;T&quot;},<br>                                [Character]<br>                              )<br>                              then &quot;3&quot;<br>                      else if List.Contains(<br>                                {&quot;L&quot;},<br>                                [Character]<br>                              )<br>                              then &quot;4&quot;<br>                      else if List.Contains(<br>                                 {&quot;M&quot;,&quot;N&quot;},<br>                                 [Character]<br>                               )<br>                               then &quot;5&quot;<br>                      else if List.Contains(<br>                                 {&quot;R&quot;},<br>                                 [Character]<br>                              ) <br>                              then &quot;6&quot;<br>                      else null<br>                    else null                           <br>                  )<br>                in<br>                  soundexChar<br>              }<br>            )<br>    )<br>  )</pre><p>The code applies a table transformation, then a nested record transformation, singling out the <strong>Soundex</strong> column for update. Doing it this way allows us to access the other records in the row fairly easily.</p><p>This means that we can use the current <strong>Index</strong> columm value and subtract 1 from it to access the previous row in the table and compare it.</p><p>The sizeable if..else branching has been done due to the lack of a switch (or similar) structure in M, but our algorithm is simple enough that it isn’t too much of a problem here.</p><p>At this point, our query now looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/365/1*AaApMwx1RTEF1d3FWxlJ6A.png" /><figcaption>Working table after mapping (noting ignored vowels)</figcaption></figure><p>As tihs doesn’t clarify the consecutive letter situation very well, let’s take a short detour and look at what happens if we submit <strong>Paynnton</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/363/1*CU_RyqpBeoCR7v949iFv4A.png" /><figcaption>…we get points for the first ’N’ but not the next, which is what we want :)</figcaption></figure><h4>Removal of Unmapped Characters</h4><p>As our excluded characters aren’t needed in the generated soundex code, we just remove any null entries from the result set:</p><pre>#&quot;Removed Placeholders&quot; = <br>  Table.SelectRows(<br>    #&quot;Processed Characters&quot;,<br>    each [Soundex] &lt;&gt; null<br>  )</pre><p>Our table now looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*OICvmRy5PCQZeeLEowNt9A.png" /><figcaption>Characters that managed to stay on the island, and their mappings</figcaption></figure><h4>Final Preparation of the Digits</h4><p>Now that we’re done mapping, we just need to do the following:</p><ul><li>Flatten down the digits to a continuous string of text.</li><li>If the flattened string is more than 3 digits, cap it at 3. For example, 52314 would resolve to 523.</li><li>If the flattened string is less than 3 digits, pad the end with zeros. For example, 1 would become 100, or null would become 000.</li></ul><pre>#&quot;Soundex Digit Combiner&quot; = Combiner.CombineTextByDelimiter(&quot;&quot;),</pre><pre>#&quot;Soundex Digits&quot; = <br>  Text.PadEnd(<br>    Text.Start(<br>      #&quot;Soundex Digit Combiner&quot;(<br>        Table.ToList(<br>          Table.SelectColumns(#&quot;Removed Placeholders&quot;, {&quot;Soundex&quot;})<br>        )<br>      ),<br>      3<br>    ),<br>    3,<br>    &quot;0&quot;<br>  )</pre><p>We firstly create a Combiner with an empty delimiter. This is used to join our list back together.</p><p>The inner Table.ToList is fed into the invocation of this Combiner, which is wrapped with a Text.Start to get the first three characters of the flattened string. The outer Text.PadEnd adds the extra zeroes if the inner string is less than the required minimum length.</p><h4>And Finally… the Generated Code</h4><p>For this we just stick together the first letter with the three digits we’ve derived:</p><pre>#&quot;Soundex Code&quot; = #&quot;First Character&quot; &amp; #&quot;Soundex Digits&quot;</pre><p>And we’re done!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=71169e0bd24f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/implementing-soundex-in-power-query-m-71169e0bd24f">Implementing Soundex in Power Query (M)</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Dynamic Filtering with Power BI “What If” Parameters]]></title>
            <link>https://medium.com/dm-p/dynamic-filtering-with-power-bi-what-if-parameters-43ab4953de68?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/43ab4953de68</guid>
            <category><![CDATA[data-visualisation]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Mon, 14 Aug 2017 22:39:04 GMT</pubDate>
            <atom:updated>2017-08-16T03:14:49.438Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>With the recent <a href="https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2017-feature-summary/#analytics">introduction of “What If” parameters</a> for Power BI, I thought I’d play with them a little bit and pondered whether they could be used to improve dynamic filtering of visuals. One thing I thought would be cool was if you could create a variable (a threshold, for example) and make your chart filter out everything underneath that. I could previously solve it with a bit of creativity but putting this into the hands of the user was a little out of my reach.</p><p><em>(Having now seen how the “What If” functionality works under the hood, I think that these limitations may have been more my ability to explore the DAX language feature set, but I digress…)</em></p><h3>The Problem</h3><p>For the purposes of illustration, I have some sales data and I want to be able to use a slicer to look at sales by different thresholds, so starting with everyone and then using a slicer to set a “sales over $N” threshold and having my visuals responding to that. Data is 1,000 rows, generated via <a href="https://www.mockaroo.com/">mockaroo</a> and looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/1*1Uyv2I6ZMC93HFxNipu_eg.png" /><figcaption>Sample of test data generated for demonstration purposes</figcaption></figure><p>I’ll work through where I got to previously and then expand upon this to bring in the new functionality, but here’s the finished recipe:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/623/1*dg8dam4eWERjE4FDRCs6qg.gif" /><figcaption>Threshold filter parameter tied to slicer</figcaption></figure><h3>Initial Exploration</h3><p>Firstly I plot my sales by customer and I get a pretty busy chart:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/645/1*6cgRu5awFRqU-JDZwAoSVg.png" /><figcaption>That’s a lot of scrolling…</figcaption></figure><p>I can pop a filter in the visual to give me all customer with Total Sales ≥ 6 million and this looks a bit better:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*iiGn9N8Q5cnXnU4I_60OBA.png" /><figcaption>Adding a filter to the visual</figcaption></figure><p>This is still static and very much depends on me, the author, deciding what the viewer might wish to see. My users might want to explore this a bit more with their own threshold.</p><h3>Adding a Filter Measure</h3><p>Currently the filters don’t support the use of variables, but I could use another measure to use as a threshold. Taking the above limit, I can create a measure like the following:</p><pre>Company Eligible Sales = <br>    IF(<br>        [Total Sales] &gt;= 6000000,<br>        1,<br>        0<br>    )</pre><p>This will return a 1 if the sales are on or above our threshold, but a 0 if not. The reason we use 0/1 is that the next step currently doesn’t work for TRUE and FALSE, unfortunately.</p><p>I can now add this to my visual and filter on it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*f83M-Q-Lv5fQDkOUkRfqCQ.png" /><figcaption>Filtering by our measure</figcaption></figure><h3>Adding Interactivity</h3><p>We’re getting better in that we’ve moved the number out of the filter but it still requires a report author to edit the measure it depends on. With the recently-added “What If” parameters, we can do better:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/448/1*nJqmQa5Jaalf8Qg3Cojx4w.png" /><figcaption>Parameter, ranging from 0 to 10 million, incrementing by 1 million, with a default of 5 million</figcaption></figure><p>This creates a table in my model with two columns:</p><ul><li>Sales Threshold, which contains my series data as specified above;</li><li>Sales Threshold Value, which is a measure calculating the selected value from the Sales Threshold parameter.</li></ul><p>I also get a slicer in my page and I’ve positioned this above the chart:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*7QfutmRtU_Sl76zPLJTB2A.png" /><figcaption>Slicer positioned up with chart</figcaption></figure><p>The slicer isn’t doing anything yet, so I now need to make a change to my Company Eligible Sales measure as follows:</p><pre>Company Eligible Sales = <br>    IF(<br>        [Total Sales] &gt;= &#39;Sales Threshold&#39;[Sales Threshold Value],<br>        1,<br>        0<br>    )</pre><p>This simply binds the 0/1 value used in the filter to the selected parameter value. No further changes required!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/623/1*dg8dam4eWERjE4FDRCs6qg.gif" /><figcaption>There we go!</figcaption></figure><h3>Wrapping Up</h3><p>This is a simple use case and could be attempted with any type of measures for different results. It’ll be interesting exploring this functionality a bit more as time allows but hopefully a few people may find this handy, or find a more elegant solution with a bit of experimentation.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=43ab4953de68" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/dynamic-filtering-with-power-bi-what-if-parameters-43ab4953de68">Dynamic Filtering with Power BI “What If” Parameters</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power BI Local Timezone Issues… and Beyond]]></title>
            <link>https://medium.com/dm-p/power-bi-local-timezone-issues-and-beyond-a23e2fcffb53?source=rss-dcfbf31b00d6------2</link>
            <guid isPermaLink="false">https://medium.com/p/a23e2fcffb53</guid>
            <category><![CDATA[report]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[dax]]></category>
            <dc:creator><![CDATA[Daniel Marsh-Patrick]]></dc:creator>
            <pubDate>Sun, 05 Mar 2017 19:12:48 GMT</pubDate>
            <atom:updated>2017-03-05T19:15:27.591Z</atom:updated>
            <content:encoded><![CDATA[<h3>Power BI Local Date Issues… and Beyond</h3><p>I experienced a fairly common problem recently when working with local dates in Power BI that has been well-defined and explained elsewhere online, but once I solved this I had some knock-on effects that other people might also have experienced.</p><h3>The Problem</h3><p>I was recently working on a Power BI visual with a rolling date chart for the last 7 full days. This would mean that if I’m looking at the chart at, say 10AM on the 27th of the month, then I’m looking at data for the 20th through to the 26th:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*ncChfA4VwE68Hf4W2F17zQ.png" /><figcaption>Desired 7 Day Rolling Chart Example</figcaption></figure><p>I’m lucky in that I <em>could</em> do this with my date dimension table, but for the sake of avoiding change control, I went with the following DAX expression to give me a TRUE() or FALSE() value if the date falls within the last 7 full days:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/15b41493091ab48feb23c1e135e57131/href">https://medium.com/media/15b41493091ab48feb23c1e135e57131/href</a></iframe><p>Using this, I can add a simple filter to my visual, specifying that I only want dates where this value is TRUE().</p><p>This means that as my data updates, the offset is recalculated as necessary and my chart will show a rolling 7 day window as required.</p><h4><strong>Until…</strong></h4><p>…I upload this to the Power BI service (hosted at powerbi.com). It then looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/757/1*8lpxhXvMTtjq_1K7MgjBnA.png" /><figcaption>My dates are wrong!</figcaption></figure><p>My 7 day window has moved back a day. The numbers are right, but I don’t get my desired functionality.</p><p>A quick Google search reveals the problem is simply down to the fact that <strong>my local time zone is not the same as Power BI’s and the </strong><strong>NOW() and </strong><strong>TODAY() functions won’t play nicely in this case</strong>. When I upload my data, it’s recalculated based off the time zone being used by the Power BI service.</p><h3>The (Quick) Solution</h3><p>It’s actually quite straightforward and <a href="http://radacad.com/solving-dax-time-zone-issue-in-power-bi">Reza Rad has a very good post about defining the problem and how to solve it</a>. As luck would have it, I’m also working in the same time zone as Reza so I don’t even need to change it ;)</p><p>Option 3 on that page is the only really tenable one if you want to manage daylight savings changes, so I went ahead and popped in a column to calculate the current time in my query as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*4qn5DuTubVs4M5t2tIUchw.png" /><figcaption>Custom column using Reza Rad’s Power Query (M) Function for Scraping Current Date/Time. Source: <a href="http://radacad.com/solving-dax-time-zone-issue-in-power-bi">http://radacad.com/solving-dax-time-zone-issue-in-power-bi</a></figcaption></figure><p>I can then modify my original offset DAX expression as follows:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/97ade1360072a0adefbb8367f847fdef/href">https://medium.com/media/97ade1360072a0adefbb8367f847fdef/href</a></iframe><p>And this will yield the correct results when I upload to Power BI, as the calculation is using the computed column as a reference point.</p><p>So far, I’m pretty much regurgitating the scope and sprirt of Reza’s post and I was really happy with the results but I found a few issues when making this a suitable solution for putting my report into production…</p><h3>My Date Table Query Slowed Down Significantly</h3><p>The biggest observation I had was that when I refreshed my data, the time the date table took to refresh was significantly longer than the 1–2 second window I had before.</p><p>The problem was easy enough to identify: <strong>the function to get the current local time is running for each row in the table</strong>, so that’s an HTTP request, response and scrape to process the data for each row in my ~20K row <strong>Date </strong>table. This is a bit of an unnecessary overhead.</p><p>I also got “drift” in my values as the table populated, due to the the time taken to process the data.</p><blockquote>I should probably mention here that it had occurred to me to just add this attribute to my table in the database and calculate it as part of the nightly date dimension update. Not everyone will have this option, or you might be working with a date field in your data set rather than a dedicated table. So, carrying on…</blockquote><h4>Simplifying the Current Local Time Retrieval</h4><p>I only need this value once to be able to use it with my offset calculation, so my solution was to create a single-row table for the HTTP request and join it to the <strong>Date </strong>table. I did this by using the <strong>Enter Data</strong> feature:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*q1Llbo02ZYF8TwU-retO9g.png" /><figcaption>This fella right here…</figcaption></figure><p>I then added single row as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/881/1*oa4_991u2STbDLtDg8G14Q.png" /><figcaption>Manual entry of single row into the <strong>Create Table</strong> dialog</figcaption></figure><p>I can then click on the <strong>Edit</strong> button to continue with my query, and add the function in as before. As this is a single row table, the function only runs once when refreshed.</p><p>The full query is included below if you just want to copy/paste it into a new table:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/17a38ade4680d8e4924658a729d9d57a/href">https://medium.com/media/17a38ade4680d8e4924658a729d9d57a/href</a></iframe><h4>Using the Local Time Value</h4><p>Once I’d loaded the data, I could then make a few small changes to the <strong>Date </strong>table to access the value:</p><ul><li>Add a new column to the table named <strong>Local Time Key</strong> and set this to <strong>1</strong> (which matches the hard-coded value above). I chose to do this in the query for the <strong>Date </strong>table, but you can just as easily do it in DAX once the table has been processed.</li><li>Create a join to the <strong>Date</strong> table on this value, essentially providing access to the record for each row:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/619/1*fpylCmK_PRaJvmjaMsUTNQ.png" /><figcaption>Creating a relationship for our artificial key</figcaption></figure><ul><li>Modify the formula for <strong>Days Offset</strong> in the <strong>Date</strong> table to use the related column in the <strong>Local Time</strong> table:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0af50f0eccecf5d2baa4109eeda28c66/href">https://medium.com/media/0af50f0eccecf5d2baa4109eeda28c66/href</a></iframe><p>This now has the benefit of retaining the desired functionality of the calculation and keeping the refresh time for the data roughly in-line with before we started the workaround process.</p><p>You could extend this table to include multiple time zones by adding more columns with the an appropriate HTTP request and still retain a decent amount of performance for this query.</p><h3>My Scheduled Refresh Wouldn’t Work</h3><p>More specifically, even though I had an on-premise gateway installed and working, I could not set up a scheduled refresh as the gateway would not be recognised in this instance.</p><p>This boils down to the fact that Power BI needs a gateway that contains at least each data source used in the report and as the HTTP request occurs during the query, rather than at the beginning, I hadn’t factored this in to my thinking…</p><p>Ultimately, Power BI saw this HTTP endpoint as a connection that needed to exist as a data source for the refresh to work.</p><p>To rectify this, I added in the URL as a data source in my gateway:</p><ul><li>Select <strong>Settings</strong> &gt; <strong>Manage Gateways</strong></li><li>Select ADD DATA SOURCE above the list of gateways on the left</li><li>Enter the appropriate details into the form; specifically, you want to set up the <strong>Data Source Type</strong> as <strong>Web</strong> and the <strong>URL</strong> needs to match the one you’re using in your function to scrape the data. Here’s mine for reference:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/628/1*qo_8Iq1ONvDEvR0X3xdQMA.png" /><figcaption>Data source settings for gateway</figcaption></figure><p>Re-attempting the scheduled refresh now finds a match for all my data sources and it works as desired. Happy days!</p><h3>One More Thing…</h3><p>As a small caveat, due to how data sources work in Power BI, the URL addressing scheme for <a href="http://www.localtimes.info/">localtimes.info</a> will mean that if you want to query multiple time zones, then you will need to add a data source to your gateway for each discrete URL, which can be a bit laborious.</p><p>You could consolidate this a single data source by using a site that uses HTTP query parameters to retrieve the time zone.</p><p>There’s a follow-up comment by Mike Honey on Reza’s blog post that suggests using the <em>full page</em> feature of <a href="http://www.timeanddate.com/">timeanddate.com</a>, and as luck would have it (at time of writing) they use HTTP query parameters for the time zone, which preserves the base URL and Power BI sees it as a single data source.</p><p>For example to get my NZ time, I can use:</p><pre><a href="https://www.timeanddate.com/worldclock/fullscreen.html?n=23">https://www.timeanddate.com/worldclock/fullscreen.html?n=2</a>2</pre><p>Where n is the internal ID of the time zone you want (22 is NZ). You can find the ID of the timezone you want by navigating to the world clock page on that site and inspecting the <em>full page</em> hyperlink.</p><p>We can then modify our above query for our local time column as follows:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/cf948eef70d26fdd83e97ed742b5a879/href">https://medium.com/media/cf948eef70d26fdd83e97ed742b5a879/href</a></iframe><p>For instance I could swap out the 22 assigned to the tz variable in the example above with 139 and get London. Here’s my table with both time zones added:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/424/1*JdZ6K7MB0xTlFNV7s6w_dA.png" /></figure><p>Hopefully these little tales may help you out if you get stuck, at least until we maybe get some better native handling of local dates and times.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a23e2fcffb53" width="1" height="1" alt=""><hr><p><a href="https://medium.com/dm-p/power-bi-local-timezone-issues-and-beyond-a23e2fcffb53">Power BI Local Timezone Issues… and Beyond</a> was originally published in <a href="https://medium.com/dm-p">Daniel Marsh-Patrick</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>