Tuesday, December 3, 2019

A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar - Part 1

This is the title of a live demo session that demonstrates the Microsoft Power Platform products working together to create an interactive data application. I recently had the honor of presenting this at the Dublin Power BI User Group (@DublinPUG) during my visit to the Power PlatformWorld Tour in Dublin. The slides of my regular session I gave there can also be found on my GitHub page.
I see you thinking about my title… I know Microsoft Flow is now renamed to PowerAutomate, but then my title just doesn't have the same flow of reading anymore :-)

In this blog series I will set apart all the steps needed to get this demo up and running on your own. I will use the text in this post, as well as screen shots and video's explaining it step by step. So if you're already (somewhat) familiar with Forms, Power Automate and/or Power BI streaming datasets, feel free to go directly to the video's and start building your own demo right away. Otherwise, keep reading and eventually you'll come across all the resources as well.
As this is a blog series, in Part 1 I will explain what the reason behind this demo is, and what I used it for in my presentations. I will make a start with the demo and show you how to make a Form and set up a streaming dataset in Power BI.


I think it's good to have some form of interactivity in your presentation. Whether it's by (live) polling your audience, telling a story, asking questions during your presentation or any of the other 8 ways to make your presentation more interactive, doesn't really matter.
The way I started doing this during my first presentation was by firing some questions at the audience after I introduced myself. For example, I asked what kind of job they were in at the moment and with what technologies (related to the topic of the presentation) they have worked with. Although that worked pretty well, I was asking closed questions with a show of hands, so if someone didn't participate I couldn't really notice. I wasn't counting hands for that matter. I did get a feel of the audience from my perspective so I know on what topics to focus on a little more in my presentation.


As we work with the Power Platform suite of products, with my main focus on Power BI, I thought it was a good idea to use some of that Power! So what I did was the following:
Let's go through it step by step.

Create a form

Go over to Microsoft Forms, login and create a new or use an existing form.
I'm going to create a form that I can use in my presentation, so it is focused at the topic I'm presenting on. So I would like the audience to enter their Name, Continent (of origin), Occupation and Experience. Let's see how it works.
A little side-note: unfortunately I can't change the language in Forms to English because this is disabled in our O365-tenant, so some words on the screen are still in Dutch, sorry for that.

I showed a few possible options for creating questions in a form, but you can experiment with other options yourself. Some questions (like the multiple choice answers) are returned as JSON-values so you have to be aware you have to do some calculations in the Power BI report to nicely visualize the values.

Create a streaming dataset

The streaming dataset, of type push, has to be created before selecting the workspace in Power Automate, because otherwise the streaming dataset won't show up. In PowerBI.com you can create a dataset in any workspace, also in My Workspace. You do that by clicking on the +-sign in the home screen of your workspace.

Make sure you turn on the switch for Historic data analysis when creating the dataset. That way Power BI will store the data that's sent through this data stream, and you'll be able to do reporting and analysis on the collected data stream. This switch can also be used to clear the dataset.


In Part 1 of this blog series I started with Microsoft Forms and a Power BI streaming dataset.
In Part 2 I will show you how to glue it all together with Power Automate and create a report out of the data. There are some tricks you need to know in the streaming dataset to properly show the output in your Power BI report. And lastly I will show you a trick to get your data refreshed live while respondents are answering the questions, but without refreshing your browser!

Wednesday, November 20, 2019

Quick tip: Sharing Power BI Dataflows


What rights does a user need to access a Power BI dataflow?

Just a quick post on Power BI dataflows after I saw this question on the Power BI Community forum about sharingdataflows - only via member/contributor permission?.
The user on the forum wants to know how to share dataflows with other users with the least privilege possible.
From my testing, it seems you need to provide people Contributor/Member/Admin to the workspace with the dataflow. If you choose the lowest permission Contributor - then they can't edit the dataflow BUT they can take over ownership and change the scheduling ! Not Good. I just want people to be able to connect to the dataflow.Have I missed anything ?
I thought this was just an easy lmgtfy-question, because there are already a lot of sources on dataflows:
Returning to finding the answer to this question, it turned out it wasn't that easy. There even is an open issue on the documentation on GitHub to add this information to the docs. So I ended up testing it myself.


To access a PowerBI dataflow you need either
  • access to the workspace the dataflow resides in as an Admin, Member or Contributor, or
  • Viewer role access to the workspace

Option 1, edit rights to the workspace, might not be desirable for your use case. But we have one other option: the new Viewer role that was introduced back in June of this year can also be used to share dataflows. I already wrote about the new Viewer role in PBI Desktop earlier.

When you are granted Viewer role access to a workspace where a dataflow is created, you can connect to and use that dataflow in Power BI Desktop:

Final thoughts

On a dataset you also have the ability to give the Build permission to give someone access to create content from that dataset (e.g. reports, dashboards, pinned tiles from Q&A, and Insights Discovery, but also content outside Power BI). This feature is still in preview however at the moment of writing. As the documentation says: Build permission is only relevant for datasets, for now? :-)

There is an idea that sounds like the Build permission on dataflows: Dataflow Permissions to give access to an individual dataflow.
Maybe the Build permission will also be added to dataflows in a later phase?

Monday, November 11, 2019

Power Query and The Curious Case of Keep Top Rows

The Power Query Editor in Power BI has many transformations that can be set from the UI. Keep Top Rows is one of them. I'd like to take you on my journey through this curious case I ran into a couple of weeks back when preparing for my session on Query Folding in Power BI.

Transformations in Power Query


I was preparing my demo's for my session at dataMinds Connect on Query Folding in Power BI (slides). If you're not familiar with query folding: transformations in Power Query are transformed into the native (data source) language and executed at the source for better performance. For a more thorough overview have a look at the post How Query Folding Works from Matt Allington (B | @ExceleratorBI).

If we look at SQL Server, some transformations can be folded (like Sorting, a simple Group By and Keep Top Rows), others cannot (merging two columns from the UI, using the Buffer() M-function and Keep Top Rows). See what I did there? :) Keep Top Rows on its own is a foldable transformation, it is (obviously) transformed into the TOP operator in SQL.
However, when using Keep Top Rows in combination with some other transformations it (looks like it) is not folding anymore. Your best chance is to use the View Native Query option in Power Query SQL Server Profiler to check if the query is folding or not. More on that later :)
A bit of contemplationI do think the real use case of the Keep Top Rows transformation might be mostly in development environments, where you can limit the number of rows to import for example. I still think the findings were rather odd and worth sharing. So, let's have a look at how it works.


I used import mode for the Fact.Movement table from the WideWorldImportersDW database. I then added the Keep Top (100) Rows transformation in the Power Query Editor.
Keep Top Rows

As you can see it folds to SQL and all is good for now.

View Native Query with TOP 100

Adding transformations

Let's add a transformation on the Quantity column and apply an absolute value to it.
Add Absolute value of Quantity

So now the option to View Native Query is gone. Let's see if Power Query is speaking the truth (spoiler: No) 😀
I started SQL Server Profiler and captured the queries running against the database after I Close and Apply my changes. As you can see, now both the TOP operator and ABS function are folded.
SQL Server Profiler: TOP 100 and ABS

Now let's try something different and add a filter on the Date Key. Something strange has happened, because I already filtered the first 100 rows, so only 2 dates remained after that. But when I click on the Calculated Absolute Value query step I suddenly have a lot more dates that shouldn't be there in my opinion.
Filter on Date Key

Let's add a field from the Customer dimension this time and see what happens.
Expand Customer Dimension

The query for Fact.Movement is still the same, so the TOP operator and ABS function are still folding. And this is what is sent to the database for the Customer dimension:
SQL Server Profiler: Customer Dimension with WHERE clause

The Curious Case

Now watch closely to the where clause: so it actually already filters out the Customer Key based on the steps performed earlier in the Query Editor. But this Customer dimension is a separate query that returns 1 row, which will be joined to the other query inside Power Query.
Back to that WHERE clause: that Customer Key = 0 is valid for my (broken) Calculated Absolute Value query step, not for the Kept First 100 Rows as you see in the picture above. So I actually get the wrong a different result back into Power Query than I would expect.
I see you're thinking: "But you didn't add an explicit order, right?" So the top 100 is a little arbitrary. That's true, so let's add that order by.
Add Sorted Rows

This is a slightly better result in my opinion. Because now the Power Query steps are in sync with each other. The issue with View Native Query still remains the same by the way. It stops working at the Calculated Absolute Value query step, but the query send to the database is the following, so including the ABS function:

View Native Query after Sort


I showed several variations in the Power Query Editor of using Keep Top Rows and showed that adding subsequent steps brakes the View Native Query option in the editor, although query folding sometimes does take place. And adding an order to your query before applying a TOP operator is always a good idea, not only in SQL.

The steps I used might not all be best practices when creating a data model in Power BI. In case of the join to the customer dimension for example, it might be better to model it as a separate dimension, because STAR SCHEMA ALL THE THINGS. But I used those steps to highlight the problem.

I already mentioned this 
bug feature to Patrick and Adam (members of the Power BI CAT team) from Guy in a Cube (B | @GuyInACube) when I was at the Precon at Data Saturday Holland. Patrick LeBlanc (B | @patrickdba) also hadn't seen this before and hopeully will take it up with one of his team members. I thought I'd make a write up for him to easily reproduce it.

Monday, October 21, 2019

Speaking at dataMinds Connect in Belgium

I already posted a short update on LinkedIn, but I now finally also had the time to update my blog.

Query Folding in Power BI at dataMinds Connect

I had the honor of doing a session at the Newcomer track at dataMinds Connect. This was a special track for people like me that have (almost) no conference speaking experience. The conference assigned buddies to these newcomers. For that, I'd like to thank Koen Verbeeck (B | @Ko_Ver) for helping me enormously with feedback to and try-outs of my presentation.
My submission to this conference was actually the first I did earlier this year, after that I also submitted to the other places like SQL Saturday Prague. But this was actually my first technical session with lot's of demo's.

Slides on Github

The slides were already shared with the conference organizers, but are also available via my Speaking page. I recently made some changes to the way I share them because I moved all my talks and slides to my Github. I still have some things and links to add there, but the basics are there.

I also did some calculations on the feedback I received and I was pretty happy with the average of 3.9!

Feedback scores

Half marathon Eindhoven

Like I mentioned in my session at dataMinds Connect I also participated in the half marathon in Eindhoven on Sunday October 12.
As it was relatively warm with 24 degrees Celcius, I had to shelve my plans of running a PR fairly quickly after 5K. A lot of people also were struck by the warmth and humidity that day

Power Platform World Tour in Dublin

The next stop is the Power Platform World Tour in Dublin where I'm presenting my Tips & Tricks of our Power BI implementation! I'm also doing one of the "Show & Tell" sessions at the Power Platform World Tour Meetup on the evening of October 30, organised by Ben Watt (B | @benrebooted).
I can also offer a $100 discount to all members who are part of their local Power Platform User Group community with the following code: 2019PPWT100DUB


Wednesday, September 25, 2019

Speaking at SQL Saturday Prague 2019

Last weekend I had the great opportunity of speaking at my first SQL Saturday, in the beautiful city of Prague in the Czech Republic. Together with my colleague Jeroen Schalken (B | @JeroenSchalken), we travelled to Prague to deliver our session on implementing Power BI (Premium) at Van Lanschot Kempen.

I also was very active on twitter during the weekend on the hashtag #sqlsatprague, I embedded every first tweet of my daily threads in the blog.


We started Friday morning with our travel to Eindhoven Airport. After a little delay in Eindhoven, we were just in time to drop our bags at the hotel and get ready to be picked up for the speaker's dinner. It was in restaurant Kuchyň, with a beautiful view over the city next to Prague Castle.
We shared some starters on the table and then could pick our own food right from the kitchen, which was very nice. We were very well taken care of by the SQL Saturday Prague team!


I started the SQL Saturday with a short run around the convention center and through the park and after having breakfast in the hotel we headed to the conference.
During the day I attended these sessions:

There was also time for relaxing in the speaker room and taking some fresh air outside. And then finally of course our own session at the end of the day:

Tips and Tricks of a Power BI (Premium) Implementation at a Financial Institute by Jeroen Schalken and me. Because we were the latest slot of the day quite a few people already left, but our audience was very interactive and had lots of questions, so thank you!

We also went to restaurant Na Pekařce for the SQL Saturday after party, where we were joined by the speakers, organisers and also around 40 attendees.


We had this day to enjoy the beautiful city of Prague, together with a few other speakers and Jarda, who was our awesome guide for the day!


Because our flight was at 9.30 AM we started this day very early with a quick breakfast and on to the metro and bus to the check-in and security at the airport. It all went very smoothly so I even had some time to work on this post. At 11 o'clock we were back again on Dutch soil. As a surprise my wife and 2 youngest kids were there to pick me up.

Closing words

All-in all it was a wonderfull weekend and I met many new great friends from the SQL Community. I hope I can some day go back to (SQL Saturday) Prague!
I also made a short video of my adventures during the weekend, so enjoy!

Wednesday, September 18, 2019

Power User Days Belgium - September 2019 Edition

This weekend I attended the Power User Days in Belgium, organised by the Flemish Power BI User group together with the PowerApps and Flow User Group Belgium. It took place at the Bmatix office in Kontich. I not only attended, but also did 2 sessions on Power BI!

TL;DR - Slides

For those of you that attended my session or the workshop, here are the links to my slides:

Always good to have stickers!

My experience

I had one session planned, my Tips and Tricks of a Power BI (Premium) Implementation at a Financial Institute, that I'm also presenting together with my colleague Jeroen Schalken (B | @JeroenSchalken) at SQL Saturday Prague next weekend. But Jan Mulkens (B | @JanMulkens) (founder of the Flemish Power BI User Group, a.o.) contacted me last Wednesday with the idea of doing a second session in the form of a 1-hour hands-on workshop. Jan quickly setup a demo Power BI tenant for me and I came up with the content of the workshop in the next few days.

I really liked doing the workshop, although it was a last minute change of plans. I like the interactivity you get with and between the attendees. The login and setup stuff took a bit longer then expected so I quickly ran out of time. However, almost everyone decided to stay another 15 minutes to complete the exercise I started, even though lunch was already being served :-)

Altogether I really enjoyed the day, now on to SQL Saturday Prague where I will be (co-) presenting my Tips & Tricks session at the last time slot of the day!
Let me know if you're also going to Prague, otherwise see you at Data Saturday Holland or dataMinds Connect!

Thursday, August 29, 2019

The New Power BI Workspace Viewer Role Explained

Back in April of this year Microsoft announced the rollout of a new Viewer role in Power BI workspaces. It is mentioned in the release notes of April of this year: Power BI Planned features

On June 25 the Power BI team announced therollout of the new Viewer role for Power BI workspaces.
This is all part of the newworkspace experience, which in fact is not that new anymore :) It in fact has become the default workspace you create, there will be a plan to migrate old workspaces to the new ones. You can read the docs on howthe new workspaces are different or on workspacefeatures that work differently.

As of now we have 4 roles available in a Power BI workspace. The new Viewer role works quite the same as the classic workspace option "Members can only view Power BI content". It gives a read-only experience to the users.
A few remarks that weren't totally clear for me in the beginning:
  • You can have read-only access to the content in the workspace without publishing an app.
  • The Viewer role does requires a Pro license or your content must reside in Power BI Premium capacity. That way it behaves the same as the published app on a workspace in Premium capacity.
  • Row-level security (RLS) on the datasets in the Power BI workspace is enforced for users who only have the Viewer role.
  • Users can only export summarized data.
  • If you want a user with a Viewer role to Analyze in Excel, they need Build permissions on that dataset.
  • Build permission on a dataset also means they can export the underlying data that's used to build the visual, so you might be careful before granting that access, especially with RLS.

There was an improvement to the export of summarized data with the Build permission. If you're using that or are planning to give users the Build permission, please do read the following blog post by Lukasz Pawlowski: Change to Summarized Data Export behavior with Build permission.

Here is an overview of the capabilities of the four roles: admins, members, contributors, and viewers:

So let's take a look at the experience in the Power BI Service. I tested everything myself to be sure, so these are all pictures from our own tenant.
When granting access to a user you can choose from one of the 4 roles:

In the new workspace you can choose to add individual users or add security group, where the latter is the best practice. That way you can manage the users and their access outside of Power BI.

As a Viewer, you only see the Dashboards, Reports and workbooks, in either view of the workspace. So the Datasets and Dataflows are not visible.

This is the File menu when logged in as a Viewer. So you can't download the pbix-file, as that would give you access to the underlying data.

This is the top level content of the workspace when logged in with the Viewer role.

As an Admin in the same workspace, you not only see the Datasets and Dataflows, you also have a lot more options like Usage Metrics, Settings, Delete, etc.

In a next blog post I will show how the Row-Level Security works together with the Viewer role and the Build permission on a dataset.

I hope this makes it a clear overview of the capabilities of the new Viewer role. If you have any other use cases or anything to add please let me know in the comments!

Wednesday, August 21, 2019

Add a Linked File in a Visual Studio Project: Add As Link

Yesterday I had to add a file into a database project as a link to another file. So not a copy of the file, but a link so I can use the file in multiple database projects and only have to maintain it once. Because I couldn't easily find the solution to this and I was searching on Google but also couldn't find it quick (enough to my opinion :)), I decided to write a short blog post.
I didn't know the exact naming of the property, so the majority of the links I got from Google where Linked Server items, only after changing the search phrase 3 or 4 times I found this entry by Grant Winney.

The normal icon that appears when you add a file is the following (at least when you don't have changes in source control, here TFS). A little blue lock is displayed, letting you know that it is a local file and it is checked-in in source control.

The other available icons would be:
A plus sign for a newly added file, not yet in source control:

A red check mark, for a changed file already in source control.

TL;DR - Solution

The trick to add a file as a link is not in the menu options in Visual Studio itself, that's also where I started searching.
You have to first add an existing item to your desired folder, as shown below.

After selecting the file, don't click Add, but click the dropdown next to the Add button. There you can select Add As Link.

Et voilá, we've got ourselves a linked item, with the new icon that looks like a hyperlink :)

Let me know if you have any remarks and found this post useful in the comments!

Tuesday, April 9, 2019

My Power Platform Summit Europe Recap

Last week the Power Platform Summit Europe was held in Amsterdam. I was there together with my colleagues Sander and Pim and next to the learning experience the conference gave me, it was really nice to join them!

Before starting my recap I’d like to point you to the following tool: Microsoft Office Lens, it’s available both on iOS and Android! In every session I was in there were always people taking pictures of the slides. I sometimes do it also, just because I want to make a note next to the slide or because it’s something in a demo I’d like to capture. But the point I’m trying to make here: do it with the Office Lens app, because then your pictures will look like this:

The information/sessions that stood out the most for me:

Line Krogh talked about Brons, Silver and Gold Power BI report templates:

  • Gold: created by BI team on a managed Tabular model
  • Silver: not created by BI team on a managed Tabular model
  • Brons: not created by BI team on non-certified data sources

The Art of Gathering Effective BI requirements by Mico Yuk

  • An interactive session where she also shared her spreadsheet to be used in discussion with end-users

Nikhil Gakwaed showed us some nice improvements in the Power BI Service:

  • Comments on dashboards
  • Organizational branding for Power BI Home

Callum Green did a session where one part about data profiling was interesting in particular:

  • Data profiling in M (Table.profile)
  • He also did some tweaking afterwards to get the profiling on the column and value level

The Value of Self-serve BI in a Large Organisation by Rishi Sapra:

  • What-if scenario builder, which he will explain in-depth at the virtual Power BI Days

Demo of splitting the report and the data model by Just Blindbaek:

  • This will be even better when Shared and Certified datasets are available, hopefully later this month!

Power BI Performance by Kasper de Jonge, David Magar and Bhavik Merchant:

  • Avoid Bi-Di crossfiltering: you will go to hell! (according to Alberto Ferrari)
    • Instead turn crossfiltering on in DAX: CROSSFILTER(Dim, Fact, OneWay/Both)
  • Use variables, especially with IF
  • Don't use IFERROR
  • Use DIVIDE
  • Model Star-schema/Snowflake
  • Filter on Dimensions

A great session on Aggregation Tables by Reza Rad:

  • Import mode, Direct Query and Dual
  • Use DaxStudio to analyse the queries send by DQ

At the end of day 2 we had a nice view of the entrance to the conference:

In the morning I found some time to go running with Sander in the Amstel Parc just across the road from our hotel. Although it was very chilly, it was worth the early wake up!

Let me know if you agree with me or if you think you liked other sessions more at the Summit.

My next post will be a recap of the Power BI User Day 2019 in Utrecht where I also gave a presentation together with Jeroen Schalken.


Tuesday, March 19, 2019

Power Platform Summit Europe & Power BI UG

Power Platform Summit Europe

This year the Power Platform Summit Europe (formerly Data & BI Summit) will be held in Amsterdam in the RAI, on 27-29 March. They renamed it because the conference is focused on Power BI, Microsoft Flow and PowerApps.
I will again be attending this conference (you can read my blogs about last year about my announcement, the Day 1 keynote and the recap). While it is good to have dedicated time to learn from the sessions at the conference, I always find it very useful to get in touch and network with everybody there.
I hope I also get the time to get some blog posts out during the conference.

The tracks this year are Act, Administrator, Automate, Developer and Measure.

The mobile app for the conference is now also available (for Android and iOS), where you can:
  • Build your personal schedule
  • View session details and presentation materials
  • See who else is coming to the conference
  • Get updates or room changes
  • Give feedback on the sessions
As last year, Microsoft added the Power Series to the conference, some of which I am very interested in. There will be over 15 hours of deep dive and hands on content. A few of the topics covered in the Power Series are:
  • Data Flows
  • Power BI Admin & Performance Tuning Deep Dive
  • Power BI Embedded Deep Dive
  • Debugging DAX and Model performance issues
  • AI in the Power BI Service

Power BI User Group Day 2019

Immediately after the Power BI User Group Day 2019 will be held on Saturday March 30 in Utrecht, where my colleague Jeroen and me will be presenting about our journey of implementing Power BI (Premium) at Van Lanschot Kempen!
I assume a lot of the speakers and attendees of the Summit will also attend this UG. The keynote will for example be provided by Charles Sterling.

I think it will be a great week and it will be good to catch up with friends and make new connections in the community.
If you’re also going to one of the above events, let me know in the comments or on Twitter, I hope to see you there!

Tuesday, January 29, 2019

19th Power BI UG Netherlands (November 2018)

I’ve been meaning to write this blog post for some time now, but I never got to finish it. A little bit late, but here it is!

On November 29, the 19th Power BI User Group was held at our office in Den Bosch. Yes, that’s where the delicious “Bossche bollen” come from. This UG was officially called the Dutch Data Dude farewell tour, because @JeroenterHeerdt has moved to America to join Microsoft in Seattle, as you can read here.

After some delicious sandwiches, snacks and “worstenbroodjes”, Jeroen Schalken and I kicked off the evening with an introduction to data management and the introduction of Power BI at Van Lanschot Kempen.

Michiel Rozema and Henk Vlootman (Quanto) explained how to make financial reports with Power BI. A lot of organizations are used to financial reports in Excel. Readable for the accountant, but less accessible for management. How do you use the strengths of visualization of Power BI to create strategic as well as operational information?

Jeroen ter Heerdt closed the evening and took us on a ride through his world of data visualizations: from pie charts (do NOT use them) to the three roles he thinks are needed for data management:
  • Data Engineer
  • Data Scientist
  • Data Artist.
Making a good data visualization can be difficult. Jeroen showed us the do’s and don’ts to make even better visualizations.

We had a wonderful evening with some great conversations amongst the attendees. Thanks @PBIG_NL for the chance to host this evening!


Featured Post

Governance & Administration - Tenant Settings: Searching

With all the Fabric announcements in the last months, some of the Admin announcements might have slipped through. As you might know, the Adm...