Finding (and Keeping) Motivation Is Hard - You Are Not Alone

Hello, and welcome to my new blog.
All jokes aside, it's really been a while since I last wrote published a blog post..
I just didn't have the power or motivation to finish any post.
I'm even wondering if I do have it now.
I did start quite a few posts, but they very soon became a bit too long, or I lost attention.
And after one or 2 writing sessions I just stopped and moved on, to the next post I wouldn't finish.
That's been going off and on for about a year I guess.
I wrote published 2 posts in 2021 so far, that says it all.

After finishing the actual Power BI post and this prelude, I decided to split them up into 2 separate posts.
This post has become more of a motivation/#pbifamily post, the second part is about the November 2021 Power BI Desktop update.
This does mean you'll have to wait a bit longer for an in-depth Power BI post. But that actually gives me the chance to create another post in a very short time frame. 😀

But... We're all in this together

Going to Power BI Next Step in September, and all the fun and learnings I had, really did get my attention and created some sparks for new energy on writing and speaking, even on new topics. But as you have guessed, that also didn't quite come through all the way.

I guess doing a major home improvement project also doesn't help, right?

Exciting times, because we are now 1 month into our home improvement project! 🧱🏠
From start...
1/2 pic.twitter.com/tKKfrz3vvc

— Nicky van Vroenhoven (@NickyvV) October 6, 2021

Eventually, it turned out really great, I'm writing this blog post out of my new office! The office is not quite finished all the way yet, and there's still a lot of work to do before everything is finished, but a major step up already!

We are getting somewhere.. Now for some decorations :-) #homeoffice #pbifamily pic.twitter.com/arTzs7ViTC

— Nicky van Vroenhoven (@NickyvV) November 26, 2021

Re-energize

I also get a lot of help from and inspiration for running in these times, from all my buddies at #runhappy:

19k early this morning! #runhappy Happy Sunday 🌞 @erinstellato @SQLBalls @SQLRockstar @wstrasser @bweissman @SQLFlipFlopsDBA @heymiky @kjonge @cporteous @transmokopter @landrews5807 @widowpage @ko_ver @sqlespresso @sqlworldwide @mwdarab

— Nicky van Vroenhoven (@NickyvV) November 28, 2021

Thank you for that, Erin, Bradley, Thomas, Wolfgang, Ben, Jim, Michael, Kasper, Craig, Magnus, Leslie, Lynn, Koen, Monica, Taiob, Mohammad, and others that occasionally join!

It's important to get new energy, in whatever way you do that. I love to go running 150k+ per month and have regular stand-up or lunch walks, but do whatever suits you. Just make sure you go away from your screens once in a while every day. Go play with your kids/dog/cat/spouse for a few minutes 😉

You are not alone

Luckily I know I'm not alone in this, .
This tweet from yesterday actually sparked my interest in writing a new blog post:

I wonder whether the pandemic has changed me permanently, or whether it's because we're still in the middle of it. I don't have the same energy or motivation as I used to, even if I wish I did. Not as productive. Do just need to relearn habits? Get myself together? Hm 🤔

— Cathrine Wilhelmsen (@cathrinew) November 30, 2021

That's why I do think that December can be a turnaround. Let's at least try, right? 😃

The End

I wrote this post almost in one go (alright, I was distracted a few times when searching for the tweets I wanted to embed 😀).
It's nowhere close to anything I've ever posted.
Way out of my comfort zone.
But if it helps only one person, I'm happy.
And if not, it actually helped me already.

If you need motivation/help/anything: reach out.
Leave a comment here, on Twitter, on LinkedIn, in real life, whatever suits you.

Have a great day! 💛

Back In Business: Power BI Next Step

So, I did a presentation last Friday on Query Folding in Power BI.
It was at the Power BI Next Step conference.
You might think it was another online, like the 2.463.345th, webinar via Teams. But...

WE WERE IN COPENHAGEN!! 😁

Meeting people again, having a chat and drink with other speakers, attendees and #pbifamily friends I haven't seen other then online... It was A-MA-ZING to be back at it! 

Because a few months ago there was still some uncertainty about all the rules, Marc, his girlfriend and me decided to take a road trip and go by car, which was pretty doable and actually quite fun.

I also went for a little run just after the conference!

We also had a lovely speaker dinner in the oldest restaurant in Copenhagen: Det Lille Apotek:

Finally, a speaking dinner again! 😀🥳 #PowerBINextStep @pkamasani pic.twitter.com/G37M5P9tIA

— Nicky van Vroenhoven (@NickyvV) September 17, 2021

And on Saturday we finished it off with a little boat trip:

Out on a lovely boat trip with all the #pbifamily still in Copenhagen! 💛 pic.twitter.com/wY4wgxVTuL

— Nicky van Vroenhoven (@NickyvV) September 18, 2021

 

Slides and more...

The conference will send out all the slides, but if you can't wait for mine, you can find them at my GitHub page, or directly download the slides as PDF here.

During the conference, several people asked if the information from my session was also available on my blog somewhere, which actually is not (yet 😀).

So with renewed energy from the conference, I'm announcing my next topic here:

A blog series on Query Folding!

Keep an eye out for the first part in the next weeks!

Power Platform 2021 Release Wave 1 Plan

The Power Platform 2021 Release Wave 1 Plan has just been released!

You can go to the plan in the documentation (here) or download the detailed PDF (here).

I'd like to give you a quick summary of what I think are the most exciting features coming up in this wave. Remember though that this plan focusses on the period from April to September 2021, so features releasing before that will not be taken into account here. For example: Object-level security is a feature planned for Public preview in February 2021. For a complete overview have a look at the 2020 release wave 2 plan.

My primary job focus is with Power BI and Power Platform governance, so as you may understand, the majority of features will be in that direction.

I also display the Public Preview (PP) and General Availability (GA) dates here, just to have a complete overview. Please be aware that this is a post on my blog that is accurate at the time of writing. For the most recent status of features please go to the 2021 Release Wave 1 Plan.

A little disclaimer before we start: Because this post lists features that may not have released yet, delivery timelines may change, and projected functionality may not be released. For more information, go to Microsoft policy.

Power BI

For an overview of all Power BI related new and planned features, go here. These are my favorites:

OLS permissions for dataflow entities (PP - | GA May 2021)
This will allow you to give permissions to specific entities inside a dataflow, so you don't have to share the whole dataflow anymore.

View permissions for dataflows (PP May 2021 | GA - )
This allows owners of a dataflow to give view permissions, so users can browse entities in a dataflow. Combined with the previous feature, you can now share a specific entity with view access.

Workspace level refresh for datasets and dataflows (PP May 2021 | GA - )
This is HUGE! This feature will take care of refreshing all artifacts inside a workspace, also taking care of dependencies and orchestration of those refreshes.

Deployment pipelines - dataflows support (PP May 2021 | GA - )
This feature describes itself, although there are 3 sub features mentioned:

• A way to visually compare the code between 2 versions of a dataflow
• Deploy dataflows through the stages
• (!)Maintain the relationship between dataflows and datasets through the pipeline

Background validation for dataflows (PP - | GA April 2021)
When saving a dataflow, the validation will no longer be blocking for the UI and be performed in the background.

Data lineage, impact, and API enhancements (PP April 2021| GA - )
While there are a few capabilities already available (lineage view in the workspace, dataset impact analysis), the below will also be added:

• Composite models in lineage view
• Admin API's to scan artifacts and lineage cross tenant
• An API that returns the list of workspaces that changed since the last API call
• Extending Service Principal authentication for Power BI read-only API's

Power Automate

For an overview of all Power Automate related new and planned features, go here. These are my favorites:

Improved onboarding experiences for new users (PP March 2021 | GA April 2021)

Improved licensing and usage visibility for Power Automate environments and tenants (PP March 2021 | April 2021)
This visibility will be provided in the Power Platform Admin Center (PPAC) and the flow analytics page.

Admin reports to show API usage for users and flows (PP April 2021 | GA - )
There will be license reports in the PPAC to view the API usage of flows and users, giving admins the capability to determine when flows or users are approaching usage limits.

Governance capabilities to prevent data loss (PP April 2021 | GA - )
There will be more governance capabilities in the PPAC including:

• Connector restrictions
• Connector action restrictions
• Endpoint filtering
• Restrictions for sharing connections

Governance and administration

For an overview of all governance and administration related new and planned features, go here. These are my favorites:

Power Platform tenant isolation support (PP April 2021 | GA - )
Enable/Disable tenant isolation using self-serve capability through the PPAC. Previously, we had to create a support ticket to enable tenant isolation.

User security configuration experiences in PPAC (PP April 2021 | GA - )
Managability of users and security will be better and centralized in PPAC, with a modern and intuitive UI for environments with a Dataverse database. Also AAD-groups will be included in the security role assignments

Tenant scope admin report for Power Apps usage (PP April 2021 | GA - )
Power Platform service admins will be able to understand usage with metrics like Monthly Active Users (MAU), the kind of engagement with new and return users, and also the list of apps in use and not in use at the tenant.

Tenant-level admin reports for flow usage (PP April 2021 | GA - )
Similar to the previous feature, but now for Power Automate. This will also provide useful insights, primarily in the field over number of (successful and failed) runs, number of makers and also total flows, number of flows created in the last 30 days, etc.

Data Integration

For an overview of all data integration related new and planned features, go here. These are my favorites:

Data source management enhancements (PP - | GA September 2021)
This is a LOOOONG awaited feature, at least from my side! :-) While the PPAC now already shows Power BI gateway data sources in read-only mode, centralizing the management will be enhanced further, a.o. by:

• visibility of the username and service principals of a data source,
• a new field called Data source description,
• Installed date, and Last used date for the gateway,
• and support to use groups instead of users. 

Email Alerts (PP - | GA September 2021)
For me, the best enhancement here is to set up email alerts to notify for updating to a newer version of the gateway.

Reuse and share queries across Power Query clients with Power Query templates (PP September 2021 | GA - )
While not immediately helpful for me (because I don't work that much with PQ), I do see the great potential for this feature!

Closing

All-in all a great set of features to be working on. I can't wait to get my hands on them!

Next to all this Power Platform goodness, there's also a lot going on in Dynamics 365, if you're interested have a look here.

Let me know what your favorite feature is or if you think I missed anything important!

My Past & Upcoming Speaking Engagements

I noticed I already did quite some presentations this year (15). So I thought this would be a nice time to give a short update on what I did and what presentations are still to come.

Past Talks

• Denmark Power BI User Group (webinar), November 4th
• MVP Lightning Talks (webinar), October 21st
• DataWeekender #TheSQL (webinar), October 17th
• SQL Saturday Denmark (webinar), September 26th
• SQL Saturday Croatia (webinar), September 19th

For a full list of all my presentations have a look at my speaking page.

A while ago I decided to create a github page to host all my slidedecks, so have a look there if you are looking for materials: https://github.com/NickyvVr/talks.

Upcoming Talks

M365 Chicago, November 13th

I know, it's Friday, and it's the 13th: what could go wrong, right? :)
I'm actually delivering 2 presentations here, which you can find below, one at 7PM and one at 10PM (UTC+1).

You can still register for this event here.

Just be aware that this is a US-based event, so it starts at 3:10 PM (CET) with the keynote: Building Community Through Storytelling and ends with an EndNote Panel at midnight my time.

Power Platform French Summit, November 20th

This will actually be a 2-day event:

• Thursday, November 19th with a Business Day
• Friday, November 20th with a Technical Day

Here I'll also be giving my Forms, Flow, Power BI session at Friday afternoon.

Closing

I would also like to mention that the next event (register here) for our Power BI Days Netherlands is scheduled on November 24th with this great line-up:

• Alexander Arvidsson (b | @arcticdba): The Untruthful Art – Five Ways of Misrepresenting Data
• Meagan Longoria (b | @MMarie): What Is Inclusive Report Design and Why Should You Care?

Finally, if you are interested in having me talk for your User Group or conference, have a look at my available sessions and let me know here or on Twitter.

List All Functions in Power Query with #shared

I decided to dedicate this short blog post on how to make better use of Power Query and all the M functions inside. Let's see how we can do that.

Thank you

But before I dive into my blog post, I'd like to thank the #pbifamily for looking out for me!

Thanks for all the help yesterday, wonderful #pbifamily, I 💛 you!

Short update: I started a blog on a #PowerQuery trick, which should be finished later today, yay 😁

If motivation is letting YOU down at the moment, have a read through this thread 👇, and know you're not alone! https://t.co/n3nhNVyD01

— Nicky van Vroenhoven (@NickyvV) November 3, 2020

#shared

In addition to what the title suggests, you can not only show all the functions, but all tables, records, (custom) functions and query (results) inside Power Query.

You can start using it by:

• Creating a new blank query and either:
• type in "= #shared" in the formula bar and press Enter, or
• open the Advanced Editor and replace everything with "#shared" and press Done
• Click on Into Table in the top left and you're done!

This will give you a list of functions (and query results, etc.) available in M.

You can search through the list of Names and e.g. see what functions you can use with week:

Opening e.g. Date.IsInNextWeek opens up the details for this function, showing you a description, the parameter used and the returned value.

You can even test it right there.

Watch it in action

If you like a more visual explanation, have a look at a recording of my Query Folding session at the dataMinds User Group earlier this year. The part about #shared starts at 52:40:

During the research googling for this blog post, I also found another thing you can do with #shared:

• The Environment concept in M for Power Query and Power BI Desktop, Part 3 by Lars Schreiber (@SchreiberLars | b) and Imke Feldmann (@TheBIccountant | b)
• Curious on how to start an embedded YouTube video on a specific time? Have a look at this blog post, you'll have to use "?start=" instead of "t=" for a regular URL

#MSIgnite 2020 - Power BI Recap

While there were several issues with the first sessions having no video, there was still a lot of excitement around all the announcements being made at Ignite! And luckily we can re-watch those sessions!

As I watched the sessions I made some notes, I cleaned them up a bit and thought I'd share them for anyone that likes to read up.

It's in no way a full update on Power BI related news, but highlights I found interesting. If you think I missed something that should be here, please let me know.

It's full of links to read more, also sometimes [Sessions] are linked where you can have a look yourself!

Enjoy! :-)

Icon or logo

So, what do you think about the new Logo? Or is it an Icon, or a …? :-)

Power BI Desktop September updates

Smart Narratives (September Desktop release)

Search capability for workspaces when publishing a report from Power BI Desktop

A small step for the Power BI team, a giant leap for the #pbifamily! :-)

Visual data preparation (coming soon) in Power Query: I'm really looking forward to this one!

Sessions

Demo by Kim Manis in [Session]Deliver insights where decisions are made with Power BI

• Power BI Service (with the whole left-side menu) rendered inside Teams
• Create quick reports by copy-pasting data

Power BI Premium Gen2

[Session] Advancing Power BI Premium for the enterprise analytics market and beyond

Wow, am I excited about this one! Of course we use Premium, so that's obvious, but also the announcement of Premium Per User is very exciting!

• Power BI Premium Gen 2
• Faster processing of refreshes
• No concurrency limitation
• No cumulative memory limitation, so each refresh can take up 25GB of RAM (for a P1)!
• Premium Per User, available at no cost during public preview, no price announced yet
• Autoscale (optional feature)
• Add (paid) vCores for at least 24h
• Billed through Azure subscription
• Set limits to control cost
• Cost-management on a workspace level
• Utilization breakdown

Have a look at the Power BI blog where Chris Finlan answers the most common questions around Power BI Premium Per User lincensing!

Deployment pipelines

This feature goes GA: read the GA blog, with an important excerpt:

• Using deployment pipelines, you can make updates to a model with incremental refresh configured, and deploy it to production, while retaining both data and partitions!
  *Only Datasets with the ‘Enhanced metadata format’ switch turned on

So, what else is coming? For example, Paginated reports and Dataflows will be supported with deployment pipelines in a later stadium. 

For all updates, I suggest you check the Power BI roadmap wave 2 release notes!

Free Certification exam

And of course, if you are attending Ignite, and complete at least one collection in the Microsoft Ignite Cloud Skills Challenge, you are eligible for a FREE certification exam! Have a look here for the details: Cloud Skills Challenge

Other interesting sessions still to watch:

And if you want to have a complete overview of what has happened at Ignite, not only Power BI, you should read the Ignite Book of News!

Refresh a Power BI Dataset with PowerShell and a Service Principal

After Installing the PowerShell Power BI Management cmdlets a few months back, I finally managed to get a dataset refresh working, initiated from PowerShell with a Service Principal. Yeej :-)

Congrats on getting this done! Would you write a blog about this adventure? 🙂

— Chuy Varela (@chuy_varela_) June 12, 2020

But as you can imagine, it didn't go without a fight (or 2, or maybe more), so I thought it would probably be good to share my experiences so you don't fall in the same traps as I did.

Use case

At Van Lanschot Kempen, we are doing daily refreshes of the corporate DWH, connected datamarts and extraction marts after that, all on-premises. Then the Power BI data models are refreshed on a time trigger (say 10 AM) to refresh the data from the datamart to Power BI. We would like that dataset refresh to be event-based. So whenever the datamart is refreshed, immediately refresh the corresponding Power BI dataset.

Setup

To get this working I've used the greatest part of this post How To Trigger A Power BI Dataset Refresh Using An Azure Data Factory Web Activity by Dave Ruijter (blog | @DaveRuijter). Although that's aimed at Azure Data Factory, most of the (setup) steps still apply to my scenario. But as you will see, others don't :-)

Permissions

At my company, I am the Power BI Service Administrator (and Power Platform Admin), so I have sufficient rights in the Power BI Admin Portal, but not in Office 365. So I needed the help of other colleagues who do have these rights.

Make sure that you have the right people aligned, or at least know where to go in case you need anything, before you start.

Steps taken

I'll outline the steps I took here to have a complete list of things I did to accomplish my setup. Again, for detailed explanations per step please visit Dave's blog post:

1. Create a Service Principal and also create a secret with that
2. Create an Azure Active Directory (AAD) group and add the Service Principal from Step 1
3. Enable the tenant setting for Service Principals and add the group from Step 2
   
4. Create a (V2!) Power BI workspace (or use an existing one)
5. Add the Service Principal as an Admin to the workspace
6. Use PowerShell to refresh a dataset

I think Step 6 actually deserves a chapter of its own, while it looks so easy afterwards, I think I struggled with this for more than 20 hours over a few weeks time.

Especially because Dave's setup uses ADF, the authentication to the REST API threw me off. In the end, I "just" had to pass the secret to a PSCredential and use Connect-PowerBIServiceAccount with the Service Principal.

Then the next step is getting an Access token, similar to the setup of using the Power BI REST API as Admin, but now using that header in the Invoke-RestMethod call, as shown below.

You can also get the code from my Github page.

As evidence, I also queried the REST API to show the refresh history :)

Next steps

At this time I do have a working solution, executing the script manually, with my secret in plain text. So there are still a few things to have a look at, which I haven't had the time for yet unfortunately:

• Add the PowerShell script to a SQL Agent Job step, which is the specific use case for me
• Make the solution secure, so don't save sensitive data inside the script or on a server
• One option is to use parameters in SSIS as Brett Powell explains in his post
• I'd like to investigate if I can use the Azure Key Vault from a SQL Agent Job and store the secret there, I'll probably have to authenticate to the Key Vault with the Agent user running the job
• Incorporate the success of the refresh itself into the solution as a step, so I can take action if it fails

Let me know if you have any comments, or know how to improve certain steps of this process :)

Resources

• PowerShell script on my GitHub page
• Dave Ruijter's post: How To Trigger A Power BI Dataset Refresh Using An Azure Data Factory Web Activity
• Brett Powel's post: Refresh Power BI Datasets With PowerShell
  
• Power BI PowerShell commandlets: MicrosoftPowerBIMgmt.Profile
• Power BI REST APIs documentation
  
• Azure Key Vault REST API reference