<?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 Martijn Lentink on Medium]]></title>
        <description><![CDATA[Stories by Martijn Lentink on Medium]]></description>
        <link>https://medium.com/@martijnlentink?source=rss-4886b39d691a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*FG3A09clOwhZf6BFmEG8Og.png</url>
            <title>Stories by Martijn Lentink on Medium</title>
            <link>https://medium.com/@martijnlentink?source=rss-4886b39d691a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 04:57:37 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@martijnlentink/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[An actual download button for Power BI that your users need]]></title>
            <link>https://medium.com/@martijnlentink/an-actual-download-button-for-power-bi-that-your-users-need-cd53cc520134?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd53cc520134</guid>
            <category><![CDATA[javascript]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[power-bi-visuals]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Thu, 10 Sep 2026 10:16:01 GMT</pubDate>
            <atom:updated>2026-09-10T10:16:01.059Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>A simple Power BI custom visual to download filtered data without first finding the three dots</strong></p><p><strong>TLDR; Custom visual is now available to download for free through the Microsoft </strong><a href="https://marketplace.microsoft.com/en-us/product/power-bi-visuals/xpedited.xpedited-pbi-download-button?tab=Overview"><strong>Marketplace AppSource</strong></a><strong>.</strong></p><h4><strong>Background</strong></h4><p>Some time ago we started a project in which we were required to use Power BI Embedded. One of the things our users wanted to do was export the data from a visual.</p><p>As a Power BI power user you probably know that this is already possible; You move your mouse over the visual, open <strong>More options (…)</strong> and select <strong>Export data</strong>. The three dots are part of what Microsoft calls the visual header, or more specifically its Options menu.</p><p>Now, this is not very difficult once you know where to look. Unfortunately, that was not the case.</p><p>The export option only appears after opening the menu, and the visual header itself can be hidden. If it is hidden, <a href="https://learn.microsoft.com/en-us/javascript/api/overview/powerbi/change-visual-commands">the ellipsis is not displayed at all</a>. So we had a useful feature that was either unintuitively hidden behind an icon or completely unavailable through the interface.</p><p>Our first approach was to explain it better. I created a short animation that showed users how to move to the visual header and select <strong>Export data</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*V5OAOG526MDR1iO5GNXDQw.gif" /><figcaption>Our first attempt; an animation showing how to export data in Power BI</figcaption></figure><p>This helped, we got less questions, but it did not solve the actual problem. And I felt we could do better, since Power BI reports are webpages under the hood, we should be able to just recreate a simpler download experience we encounter every day on the web, right? So that is exactly what I did.</p><h4>&lt;BUTTON&gt;Using HTML&lt;/BUTTON&gt;</h4><p>The result is a Power BI custom visual called <strong>Download button</strong>. You add it to a report like any other visual and assign the columns or measures you want to export. When a user clicks the button, the visual writes its current data to a CSV file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/620/1*BzQFQwcDSrqIICaR4L-HsA.png" /><figcaption>Completely custom download button, open for 100% CSS personalization</figcaption></figure><p>The useful part is that the button is a visual itself. Power BI supplies its data through the same visual data pipeline it uses for a table or chart. This means filters work automatically. If a user filters the report to one region or period, only that filtered data is included in the download.</p><p>The report author also remains in control of the export. You choose which fields to add, the button text and the filename. You can apply CSS to match the button to the report design and disable it when the filtered dataset is empty. The visual currently supports CSV exports of up to 30,000 rows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/719/1*4rd6A3IX2_BaNMbXAL-wDQ.png" /><figcaption>Works just like any other visual in your report</figcaption></figure><p>Under the hood there is not much magic involved. The visual receives a table from Power BI, converts its column names and rows to CSV, and passes the file to Power BI’s <a href="https://learn.microsoft.com/en-us/power-bi/developer/visuals/file-download-api">File Download API</a>.</p><p>Great totally fixed, we now had a proper download button that even can be customized using custom CSS! There only was one more caveat I was about to run into.</p><h4><strong>The struggle when Power BI is embedded</strong></h4><p>When I showed the download button to our stakeholders they were very happy. When however, we moved from our testing on acceptance to production, where it is embedded in our portal the button didn’t seem to do anything. Upon checking the Microsoft documents I found that the custom visual File Download API only is supported in <a href="https://learn.microsoft.com/en-us/power-bi/developer/visuals/file-download-api#considerations-and-limitations">Power BI Desktop and the Power BI service</a>.</p><p>The button did not work but the beginning was promising. I started thinking about the page where the report is embedded in and started to explore the embed JavaScript APIs. I found that, while the embedded report could not start the download, the web page around it could still listen to interactions with it.</p><p>Power BI Embedded raises a visualClicked event whenever a user clicks a visual. The event contains the name and type of the clicked visual. This gave me another route: listen for a click on my Download button, retrieve that visual from the active report page and generate the browser download from JavaScript.</p><h4><strong>JavaScript to the rescue!</strong></h4><p>The script first checks the visual type so that clicks on charts, slicers and other visuals are ignored. It then asks Power BI for the clicked visual and calls exportData() to retrieve its current data as CSV.</p><p>Finally, it creates a temporary link with a download-attribute and simulates a click. The browser handles the rest.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2b100486ba2732d87fb3d328d701f588/href">https://medium.com/media/2b100486ba2732d87fb3d328d701f588/href</a></iframe><p>In the Github repository there is a <a href="https://github.com/Xpedited-Consulting/PBI-download-button/blob/master/voorbereiding%20voorbeeldscript%20download.js">complete example script</a> also creates a filename based on the visual title and the current date.</p><p>There is one limitation worth mentioning. Microsoft currently lists custom visuals as unsupported by the embedded visual.exportData() API. The solution above works in our implementation, but you should test it with your embedding method, authentication and semantic model before depending on it in production.</p><p>The usual Power BI restrictions still apply as well. Export permissions and row-level security are not bypassed, and the export is limited to 30,000 rows. This is a shortcut to data a user is allowed to export, not a shortcut around Power BI security.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vvO4MIvOxi-QGV8oEc1Vqg.png" /><figcaption>Flow diagram on how everything works during Power BI embedding</figcaption></figure><h4><strong>Conclusion</strong></h4><p>The result is a visible download action that exports the fields selected by the report author and respects the filters selected by the user. In Power BI Desktop and the service, the custom visual handles the download itself. In our embedded application, a small piece of JavaScript completes the download from the host page.</p><p>The <a href="https://marketplace.microsoft.com/en-us/product/power-bi-visuals/xpedited.xpedited-pbi-download-button?tab=Overview"><strong>Download button</strong></a> is available for free from Microsoft AppSource. The source code is also available on <a href="https://github.com/Xpedited-Consulting/PBI-download-button">GitHub</a>, so you can inspect it, change it or contribute improvements.</p><p>If your organisation does not allow custom visuals, I have also made the original export instruction animated SVGs available in <a href="https://github.com/Xpedited-Consulting/PBI-download-button/blob/master/docs/Export%20Data%20animation%20-%20English.svg">English</a> and <a href="https://github.com/Xpedited-Consulting/PBI-download-button/blob/master/docs/Export%20Data%20animation%20-%20Dutch.svg">Dutch</a>. It still explains the hidden route rather than replacing it, but at least you do not need to recreate the same animation yourself.</p><p>Thanks for bearing with me I hope this is helpful to someone struggling with similar issues. In the end, the visual is not much more than a button. But that is rather the point.</p><h4>Do you have a problem that can challenge us?</h4><p>At <a href="https://xpedited.nl/"><strong>Xpedited Consulting</strong></a>, we like solving the hard problems where others give up, we keep exploring to find the space that separates the <strong>Im </strong>and <strong>possible</strong>.</p><p>We specialize in <strong>data science and data engineering</strong>, from Power BI and analytics to data platforms, integrations and custom solutions. If you have a data problem where the obvious solution is not quite good enough, we would love to hear about it — we love to take on the challenge!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tjhQPIe6nAGc_gcnO2xEjg.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd53cc520134" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Connecting Nest learning thermostat to an old Nefit Economy series]]></title>
            <link>https://medium.com/@martijnlentink/connecting-nest-learning-thermostat-to-an-old-nefit-economy-series-c4aca18349bd?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/c4aca18349bd</guid>
            <category><![CDATA[boiler]]></category>
            <category><![CDATA[nest-thermostat]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Wed, 29 Jul 2020 19:44:23 GMT</pubDate>
            <atom:updated>2020-07-29T19:58:13.886Z</atom:updated>
            <content:encoded><![CDATA[<p>Some time ago I purchased a Nest E learning thermostat. Now although I consider myself quite tech savvy, hardware never has been my cup of tea. So unlike every other electronic, I decided to start off reading the manual for once.</p><p>Everything seemed quite straight forward install the heat link, hook it up to the boiler, connect the thermostat to the heat link and consequently connecting the heat link to the Nest. The Nest claims to be compatible with almost all boilers. Unfortunately the protocol that our boiler used (EMS) was unsupported, and after doing some searches I found that using additional hardware I would be able to put it all together.</p><p>I found that I needed to purchase the <a href="https://www.nefit-bosch.nl/producten/thermostaten/easycontrol-adapter">Bosch EasyControl adapter</a> and <a href="https://www.cvdeals.nl/7746901847-ems-opentherm-converter-nefit.html">OpenTherm converter</a> to tie everything together. Now, although all the online resources I found were quite useful in explaining how to connect everything, I still had the feeling that I missed a simple writeup of all this knowledge into one simple set of instructions and mostly a schematic overview of everything put together. Hereby my attempt to do just this, in the hope it might just help one person.</p><h4>Order of connection</h4><ol><li>Nefit boiler</li><li>Bosch EasyControl</li><li>OpenTherm Converter</li><li>Nest Heat Link</li><li>Nest Learning thermostat</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*806FpqpruW3aI6qn5ire-Q.png" /><figcaption>(Improvised) schematic view of the setup</figcaption></figure><h3>Pictures of the setup</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*yVFyXxoaOjaiGBP0JaNuoQ.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*mCq5y7n-VQEpZic5Oc5udw.jpeg" /><figcaption>1. Nefit Economy VRC 24 boiler</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wbCpd-HOhy78WKcP2UzgKg.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*eBgJf_da2qewDH6bAofkCQ.jpeg" /><figcaption>Left: 2. Bosch EasyControl Right: 2. Bosch EasyControl and 3. OpenTherm Converter</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6pD3gQ1yHfv1ZOHKLkDW_w.jpeg" /><figcaption>4. Nest Heat Link</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c4aca18349bd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hacking Power BI to (partially) position elements outside of canvas]]></title>
            <link>https://medium.com/@martijnlentink/hacking-power-bi-to-partially-position-elements-outside-of-canvas-d22b000a0bd5?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/d22b000a0bd5</guid>
            <category><![CDATA[json]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[pbx]]></category>
            <category><![CDATA[hacking]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Sun, 22 Dec 2019 14:49:19 GMT</pubDate>
            <atom:updated>2019-12-22T14:49:19.069Z</atom:updated>
            <content:encoded><![CDATA[<h4>A bit of PBIX hacking to change x, y, width, height or other related properties which are not possible in Power BI</h4><h4>My case</h4><p>I am used in creating a visual design for the reports I create. Usually I use a service like <a href="https://figma.com/">Figma</a> for this. The biggest advantage in doing this is thinking about the overall outlook of the report. It lets you think about usage of colours, the layout of the report and removes the restriction of thinking in only Power BI visualizations.</p><p>You resulted designs often give a little challenge for exactly recreating the same thing in Power BI. But fear not! I’ve designed a lot of complex reports and managed to recreate every single one in one way or another.</p><p>A repeating theme in designing reports is using a lot of images to clarify visualisation, or sometimes, just to make it visually more attractive. Although Power BI supports images the positioning of these elements are a bit odd. It seems impossible to fully use up all the space that the element has. An example is shown below. The element is fully positioned to the left of the canvas, still there seems to be a small gap between the image and the start of the canvas, as is the same for the top of the image.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/821/1*8oVJG2TEgaydP_NlC6HxQQ.png" /><figcaption>Image does not completely fill the bounds of the visualization</figcaption></figure><h4>Partially position element outside the canvas</h4><p>Now, one might think; ‘Why not position the image off the canvas such that these visual gaps render invisible?’. Well of course this was not your thought, because we all tried this, Power BI does not support negative positioning of elements… anymore.</p><p>Do you remember the Power BI release of <a href="https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2019-feature-summary/">August</a>, when they introduced the ability to group visual elements? In the implementation of groups, the positioning of elements within that group was based on positions relative to the group. For a brief period I was able to position elements off canvas using this technique. I guess this implementation was a mistake and led to unforeseen behaviour. So in the next release they silently changed the implementation of grouped elements to absolute positioning, making it impossible again to position elements off canvas.</p><h3>Hacking to the rescue!</h3><p>Now since the update made it impossible to position the elements outside the canvas, I did notice that existing reports where elements were positioned prior to the fix would still show the elements as they were before. This taught me that although Power BI desktop does not allows you to do this using the input fields, it does support it under the hood.</p><p>If you’ve read my other posts you already know I like to hack around in the PBIX source file. So that is exactly what I did.</p><p>To make things easier you might want to rename your element that you want to position to something easy to look up.</p><p><strong>Please be aware if you try this, that incorrectly editing a file in the PBIX will corrupt your report. Make sure to always backup before you edit it!</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/783/1*ANMHuuhHLxog7J9mhK6wbg.png" /><figcaption>The report opened up in 7-zip</figcaption></figure><p>I opened up the report in 7-zip and the report pages are stored within <strong>Layout </strong>in the <strong>Report</strong> folder. If you are a familiar with JSON you’ll immediately recognize the format the reports are stored in. I started off with a editor that could pretty print and syntax highlight the JSON for me.</p><p>There is a lot to tell about the contents of the JSON. But for us the interesting part to look at is in <strong>Sections</strong>, these are essentially the different pages of your report. If you look at the <strong>displayName</strong> you can find the right page. In the <strong>visualContainers</strong>-array are the elements of your canvas. Now you could search for your the name of your element. The name is stored in the <strong>config</strong>-string. The name of my element was <em>EasyNameToSearchFor</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/937/1*77KpGrVRgcIzLqTdSb8iYA.png" /><figcaption>A visual element has two places where it stores the coordinates of the element</figcaption></figure><p>As you see, the element stores on two places the position of the element. After some testing it seems that the <em>x </em>and <em>y</em> properties do not physically position the element, the coordinates in <strong>config</strong> do. My guess is that the former coordinates are merely serialized values of the input fields.</p><p>Positioning elements outside the canvas is as easy as changing the coordinates to negative values. Now as you might know, Power BI also restricts sizing elements for widths and heights larger than the canvas, you can also bypass this restriction by changing these values.</p><p>Be sure when saving the file that the JSON is not malformed and that the encoding of the file is not changed (UCS-2 Little Endian). To finish things off, you will need to delete the <strong>SecurityBindings</strong> file from the PBIX, this is a file that in someway uses a checksum to check if the file is altered from the outside. Deleting it will ignore this check during startup of Power BI.</p><h3>Conclusion</h3><p>The result of editing the file can be seen below. A neatly positioned image right at the left top. Also note the negative positions for values <em>x</em> and <em>y </em>in the <strong>Format image</strong> pane. Keep in mind that if you change the values in Power BI desktop it will undo your positioning.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6q6F3UThxq_KT5riJISGTA.png" /></figure><p>As always, if you want this behaviour out of the box I suggest you upvote the <a href="https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/34073203-position-visual-elements-partially-outside-visual">Power BI idea</a> on this. Hopefully Microsoft will add this feature to their backlog, in the end I’m pretty curious why they decided to not allow this in the first place. If you found this post helpful consider leaving some claps, any questions feel free to contact me!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d22b000a0bd5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Fixing Fryslân in Power BI’s The Netherlands provinces Shape Map]]></title>
            <link>https://medium.com/@martijnlentink/fixing-frysl%C3%A2n-in-power-bis-the-netherlands-provinces-shape-map-ee77371120f9?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/ee77371120f9</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[topojson]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Wed, 18 Dec 2019 19:22:41 GMT</pubDate>
            <atom:updated>2019-12-18T19:27:36.163Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*fwAr7LUB1Dg5AnT5wLJZEw.png" /><figcaption>Provinces of the Netherlands, there seems to be no data for province of Friesland.</figcaption></figure><p>Recently I was looking for a neat way in visualising certain events in The Netherlands. Luckily Power BI has a nice <em>Shape Map</em> visualisation, which is a lot nicer than the ‘ol Bing Maps viz.</p><p>And the developers of Power BI even took the liberty to add our small country of The Netherlands. Creating a heat map of the Netherlands showed something odd, it looked like no data was available for the province of Friesland.</p><p>But I knew for sure there are events in the dataset for Friesland, but it seems that the name that Microsoft assigned to the Friesland is <a href="https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map#netherlands-provinces">Fryslân</a>, which is Frysian and not commonly used in The Netherlands. This issue has been addressed for a while, back in 2016 on the <a href="https://community.powerbi.com/t5/Desktop/Shape-Map-Netherlands-incorrect/td-p/54715">Power BI forums</a>. After digging some more I found out that apparently Fryslân ultimately is <a href="https://en.wikipedia.org/wiki/Friesland#Toponymy">the agreed upon spelling</a> of the province, but in day to day use people tend to use Friesland.</p><p>To fix this issue I first thought of making use of the option of uploading a custom TopoJson file using the <em>+ Add map</em> button. But it was hard to find a just as nice looking shape map as the default one. If I just could edit the name of the province the map will suit all of my needs. This is what I did.</p><p>I figured that the TopoJson file would be either stored in the installation folder of Power BI, or otherwise in the .PBIX-package itself. Since the latter was the easiest option, I started there. Opening up the file in 7zip I found the TopoJson file stored inside the <em>Report → Resources → StaticResources → RegisteredResources</em> folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*ZqcjMp4oyY5V0FpD11_cZg.png" /><figcaption>The TopoJson file inside of the PBIX file, with the incorrect naming</figcaption></figure><p>Now the easiest way in fixing this is unzipping this file to an other location. Here you should edit the file (change Fryslân to Friesland), and then inside of Power BI upload it as custom map to your Shape Map visualisation.</p><p>To save you the hassle of doing all this I took the liberty to upload a fixed version of the TopoJson of The Netherlands’ provinces. You can download it right <a href="http://g2f.nl/0llxw2k">here</a>.</p><p>If you found this helpful please don’t forget to leave some claps, any questions feel free to contact me!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ee77371120f9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automate your Power BI documentation]]></title>
            <link>https://medium.com/@martijnlentink/automate-your-power-bi-documentation-33951525c381?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/33951525c381</guid>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[power-query]]></category>
            <category><![CDATA[microsoft]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Fri, 25 Oct 2019 08:43:26 GMT</pubDate>
            <atom:updated>2019-10-25T08:54:27.290Z</atom:updated>
            <content:encoded><![CDATA[<h3>Automate your Power BI expressions documentation</h3><blockquote>Automatically generate Markdown from your Power BI Measures and M-queries.</blockquote><p>While the capabilities in Power BI keep increasing and Microsoft is fully committed in growing the platform. There are still a lot of work that needs to be done in application lifecycle management before Power BI can really excel in larger organisations, where complete teams work on data models, dashboards and reporting. Tools that work for ordinary development teams don’t seem to work too well for dashboarding.</p><p>In this article I try to do a first step in the right direction; documenting Power BI expressions.</p><h3>Requirements</h3><p>For this to work one must either have;</p><ul><li>Power BI Premium capacity, with a workspace other than <em>My Workspace</em></li><li>On-premise Power BI server</li></ul><p>Alternatively one can also run this script locally against your Power BI process.</p><h3>The basics</h3><p>Now if you are quite familiar with Power BI you might know that — under the hood — it runs a SQL Server Analysis Services Tabular Model to efficiently store and query your Power BI data set. These servers support querying for information about the model object, server connections and health status using <a href="https://docs.microsoft.com/en-us/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services">Dynamic Management Views</a>, DMV’s short.</p><p>The easiest way to query this server is using <a href="https://daxstudio.org/">DAX Studio</a>, an open source tool for performing DAX queries directly on the SASS server. I highly recommend this tool for rapid prototyping of DAX queries!</p><p>We need the following DMV’s to get our expressions;</p><ul><li><strong>Measures (DAX):</strong> MDSCHEMA_MEASURES</li><li><strong>Power Query (M):</strong> DISCOVER_M_EXPRESSIONS</li></ul><p>The server connection string can be found in the Power BI portal. Go to <strong>Settings &gt; Datasets &gt; Server Settings</strong>. Your URL should look something like this.</p><blockquote><strong>powerbi://api.powerbi.com/v1.0/myorg/My workspace</strong>;initial catalog=Customer Profitability Sample PBIX</blockquote><p>The URL consists of parts. The <strong>first part </strong>is to location of the SSAS server and the <em>second part</em>, the Initial Catalog, is the database it connects to.</p><p>In DAX Studio if you connect using this URL you can run the DMV expressions directly using a UI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sifTtMrNjRVu_uScQQe8vg.png" /><figcaption>Running DMV queries in DAX Studio</figcaption></figure><h3>Now let’s automate</h3><p>Great that we can run queries using an UI, but we came here for automation. Another way to run DMV-queries — that is also more suitable for automation, is to call them with Powershell. For this we use the <a href="https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-ascmd?view=sqlserver-ps">Invoke-ASCmd</a> command in the <em>sqlserver</em> module, which will return a response in XML-format.</p><p>Now by looping through XML we are able to construct our own Markdown table. I looked around the web and found this <a href="https://gist.github.com/andrewbbrown/140df144879eae895afcfa4cd59acf8e">Convert-ToMarkdown</a> Gist which I used as basis for my Markdown generation script.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/efeeee6fc413e5d800dd64d130c01c14/href">https://medium.com/media/efeeee6fc413e5d800dd64d130c01c14/href</a></iframe><h3>Output</h3><p>I run my script for the Microsoft’s <a href="https://docs.microsoft.com/en-us/power-bi/sample-customer-profitability">Customer Profitability</a> example Power BI report. This results in the following documentation.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3ac256af4b2337a39d616e9d1f244e1d/href">https://medium.com/media/3ac256af4b2337a39d616e9d1f244e1d/href</a></iframe><h3>Final thoughts</h3><p>The script itself is pretty cool, but requiring to run it manually every time isn’t that powerful. To fully automate documentation you could integrate the script in your production pipeline. Upon release to production your documentation is automatically updated.</p><p>Also, this post should be considered a proof of concept. While it works well, please consider security if you would integrate this script in production. If you liked this, don’t forget to leave some claps!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=33951525c381" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Use missing Skip Test Connection in Scheduled Refresh in Power BI]]></title>
            <link>https://medium.com/@martijnlentink/use-missing-skip-test-connection-in-scheduled-refresh-in-power-bi-5b3fa9a30daf?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/5b3fa9a30daf</guid>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Tue, 17 Sep 2019 12:00:32 GMT</pubDate>
            <atom:updated>2019-09-18T06:20:03.878Z</atom:updated>
            <content:encoded><![CDATA[<h3>Use missing Skip Test Connection for Scheduled Refresh in Power BI</h3><p>In my <a href="https://medium.com/p/dde6524b01c/">previous</a> post I showed a method for retrieving data from a table storage using SAS-tokens. Now when you want to enable Scheduled Refresh you will run into the following problem</p><figure><img alt="Error: Query contains unsupported function. Function name: Web.Contents" src="https://cdn-images-1.medium.com/max/761/1*PFoijwOHCDJaUhduX1thpg.png" /><figcaption>Error: Query contains unsupported function. Function name: Web.Contents</figcaption></figure><p>Meaning the Power BI Service does not support calls to the Web.Contents-function if it does not know what URL to call to. And this is because it wants you to explicitly agree that the dataset will call a certain URL.</p><p>Should be easy to fix, right? Moving the URL declaration directly into the Web.Contents-call.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c9947830af136025ba00bbaacdcc6ee1/href">https://medium.com/media/c9947830af136025ba00bbaacdcc6ee1/href</a></iframe><p>If we now verify the credentials as <em>Anonymous</em> Power BI will try to connect to the URL without the query. For our case this would be <a href="https://storagename.table.core.windows.net/tablename"><em>https://storagename.table.core.windows.net/tablename</em></a><em>, </em>this URL without the query is an invalid request and will return an <em>ResourceNotFound </em>404 HTTP status code. For it to return a valid 200 HTTP status code we <strong>will </strong>need the URL with the query string.</p><h4>Skip test connection</h4><p>After some digging around I <a href="https://powerbi.microsoft.com/en-us/blog/skip-test-connection-for-on-premises-and-cloud-data-sources/">found</a> that in April Microsoft introduced the Skip test connection checkbox. Which would not require the URL to return a valid status code in order to confirm credentials. Sadly the feature was quickly removed from the service due to a ‘few issues’.</p><p>I suspected that Microsoft is just hiding this feature in the user interface, and that it is still enabled on the backend. So my starting point was to look at the network requests it did to the server when one verifies a credential. And surely I could confirm my suspicion. Each time that you verify credentials <em>skipTestConnection</em> is passed in the request payload.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i1HIypMOyjPGm1exfkti9Q.png" /><figcaption>skipTestConnection is still passed in the request payload</figcaption></figure><p>So, how can we utilize this? We can replay this request while we change the <em>skipTestConnection</em>. Here is how to do this:</p><ol><li>Go to your <em>Data source credential</em> settings of your dataset</li><li>Open the Chrome DevTools (CTRL+Shift+I) and go to the Network-tab</li><li>And click Sign-in using Anonymous authentication and your privacy level</li><li>Now you see a new PUT request, copy this one to Powershell using the <em>Copy to Powershell </em>option in the context menu</li><li>Open Powershell and paste it in, and don’t forget to change the skipTestConnection from false to true</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/859/1*9qxQlnl3ymYlduVB-39Ieg.png" /></figure><p>Thats it! Now you should be able to setup your scheduled refresh again. Hopefully Microsoft will re-enable this feature in the future. If this helped you don’t forget to leave some claps!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5b3fa9a30daf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure Table Storage in Power BI with a Shared Access Signature Token]]></title>
            <link>https://medium.com/@martijnlentink/azure-table-storage-in-power-bi-with-a-shared-access-signature-token-dde6524b01c?source=rss-4886b39d691a------2</link>
            <guid isPermaLink="false">https://medium.com/p/dde6524b01c</guid>
            <category><![CDATA[azure-table-storage]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[api]]></category>
            <dc:creator><![CDATA[Martijn Lentink]]></dc:creator>
            <pubDate>Fri, 30 Aug 2019 08:41:02 GMT</pubDate>
            <atom:updated>2019-09-18T06:06:22.078Z</atom:updated>
            <content:encoded><![CDATA[<h4>TLDR; PBI does not officially support SAS token, using the REST API and generating continuation tokens ourselves we can achieve this. Below you will find the M-script.</h4><p>I recently got the question to retrieve data for a Power BI report from a Azure Storage account. As it is an Microsoft technology I couldn’t imagine that it would not be possible to read from it in Power BI, but playing on the safe side of things I went to check Power BI.</p><p>Sure enough <em>Azure Table Storage</em> was listed in the PBI connectors</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/656/1*p7WzQ7zPmrQGv_Sp11vhxQ.png" /></figure><p>I entered the SAS token I was provided with, but that gave me an error message pointing to the length of the URL. I ended up removing the query from the URL. Next it prompted me for an account key, I tried our SAS-signature, no luck.</p><h3>Why we don’t want to use an account key</h3><p>Storage accounts in Azure have two ways of providing access</p><ul><li>Shared Access Signature tokens (SAS)</li><li>Account key</li></ul><p>With SAS it is possible to specifically grant access to the storage with options like token expiry, permissions (query, add, update, delete) and even based on partition and row keys.</p><p>Account keys on the other hand grant full access to the Storage account, including blobs, queues, etc.</p><p>Giving out full access to everything for the purpose of reporting is obviously overkill and some organisations plainly refuse to give them out. As should they.</p><h3>How to make a SAS token work</h3><p>The most obvious solution is using M’s <a href="https://docs.microsoft.com/en-us/powerquery-m/web-contents">Web.Contents</a><em> </em>function to call the URL and using the API retrieve the contents of the table. But you’ll notice, as I did, that the Table Storage REST API has a limit of 1000 rows to return per request. The API makes use of some kind of pagination where the rest of the results can be retrieved in subsequent requests. The API requests returns headers with continuation tokens which we should provide in our next request.</p><pre>x-ms-request-id: f9b2cd09-4dec-4570-b06d-4fa30179a58e<br>x-ms-version: 2015-12-11<br>x-ms-continuation-NextPartitionKey: 1!8!U21pdGg-<br>x-ms-continuation-NextRowKey: 1!8!QmVuOTk5</pre><p>Now, as you might know we are unable to retrieve the response headers from a request (<a href="https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13959006-add-ability-to-get-responseheader-when-use-web-con">upvote</a>), in this <a href="https://social.technet.microsoft.com/Forums/en-US/4a8eb0a8-1790-4a05-919d-76a87f1123c8/http-response-headers-in-metadata?forum=powerquery#343972f1-e217-4726-8eba-451dee848065">post</a> a Microsoft employee states that this is by design for security reasons.</p><p>After looking at the keys I quickly discovered the pattern that was ‘hidden’ within. NextPartitionKey and NextRowKey a both structured the same.</p><pre>1 ! 8 ! U21pdGg-<br>↑ ↑ ↑ ↑ ↑<br>| | | | Base64 encoded row/partition key<br>| | | Separator<br>| | Length of base64 encoded key<br>| Separator<br>Static prefix</pre><p>Now with this knowledge we are able to reconstruct the tokens ourselves, I created a recursive function in M that makes a HTTP-request to the endpoint, takes the last item in the list it retrieves, constructs the tokens with the structure above. In the end all results are merged into one table. Now, recall that the NextRowKey and NextPartitionKey refer to the next item to retrieve and since our function use the last item we always have an overlap of one row with every request; <a href="https://docs.microsoft.com/en-us/powerquery-m/table-distinct">Table.Distinct</a> to the rescue!</p><h4>Show me the code already!</h4><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b9661efb067998e08968987aad93198f/href">https://medium.com/media/b9661efb067998e08968987aad93198f/href</a></iframe><p>Now here’s how to use this function. Separate your query from your URL and store them in a record, don’t forget to do a URI-decode on the query values :).</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/84357e4733d7c33eeb9046d0973bda4d/href">https://medium.com/media/84357e4733d7c33eeb9046d0973bda4d/href</a></iframe><p>Now while this may work very well for us, it is just a headache that SAS aren’t supported by Power BI, there has been an idea posted up for a few years on the <a href="https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9953427-connect-to-azure-tables-via-shared-access-signatur">PBI ideas board</a>, so all do upvote this! Now Microsoft, it is your move to make this work-around obsolete. If you liked this, don’t forget to leave some claps.</p><p><strong>PS</strong>: If you experience any issues with Scheduled Refresh using this method please see my most <a href="https://medium.com/p/5b3fa9a30daf">recent post</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dde6524b01c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>