Sadly, I can't make it to MVP Summit 2019

I’ve been holding onto some slimmer of hope, may be I could still make the trip. Alas, it’s time to face reality and write down this blog post. I’m apologizing - I’m sorry.

I can’t make it to MVP Summit 2019, I’ll need to start cancelling my various plans - flight is cancelled, there’s some hotel bookings to cancel.

I have an immediate family member who wasn’t well through 2018, we fought it, and we thought we got over it, but the problem has returned in 2019. That means more treatments and wait, and hope.

The Silver Lining

I guess I could say at least there’s a silver lining that the Australian public health cover is fully covering the treatments, if I was in America I would be on Go Fund Me already, last year.

Reach out

I will be feeling a bit sad and lonely. If you’d like to say hi - we are only separated by a Twitter DM, a Facebook message or just a Skype call.

I know I was looking forward to catching up with many of you, and may be you wanted to say hello to me, well we can totally still do that, via Skype. P.S. Call me, okay?

P.S.S remember your NDA - no streaming summit content. I want to talk to you, I don’t need to know about the latest secrets I’m sure they’ll be announced fairly soon anyway. We don’t live in a 3-year cadence world anymore.

Please Remember the new MVPs

I remember my first MVP Summit and I have no idea where to go or where to be. Please remember MVPs from your country that’s their first time, make sure they aren’t left out, and make sure please, that they get back to their hotel safely.

Flow Studio

I had hoped to use the two weeks in Seattle to really catch up with many fellow MVP friends from across the globe, and perhaps some might want to see the next iteration (or the current iteration) of Flow Studio. That will have to be done remotely. Let me know if you want to help me perfect my pitch deck. I promise you’ll get the honest truth of where we are - and why Flow Studio exists. I can’t promise I can deliver the pitch deck in 5 minutes - still working on it.

I will totally and utterly miss

• Randomly bumping into you at the LEGO store.

• Enjoying American steak with you at a steak house.

• Sipping long island ice tea unawares with you at a bar.

• Walk long trek at night across empty streets with you because the hotel is so far away.

• Getting kicked out of a karaoke bar because I’m not even drunk. But also it’s the 4th bar of the evening so perhaps it’s time to go back to hotel.

• Enjoying hot wings, oh damn the hot wings are just so nice, with you.

• The wonderful conversations I have with you.

• You Microsoftie, putting up with this horde of barbarians that call themselves MVP - most vocal professionals. You are the best and so, so brave.

• Did we just randomly wander across Seattle to the Space Needle at night and take a group selfie?

• Bellevue is actually really cold, I’m glad you gave me a really warm Fellowship jacket.

• Taking selfie photo and making them into stickers with you.

• Talking selfie photo with you using maximum Samsung beautify mode because I really need it. You don’t, but now you look really pale Sorry!

• Eating Wendy’s because the meat is square and it sticks out in the corners, and because we totally don’t have Wendy’s in Australia. Also the double dripping cheese is American heart society certified cause of death right?

• Smuggling Tim Tams for you from Australia

• Spinning around with you on the top of Space Needle

• Seeing all your happy faces

• The big giant hugs you give when you see me

• Using up all my MS store voucher, and then take your voucher because you don’t know what to do with it, so I bought another bunch of Xbox controllers. You can never have enough Xbox Controllers.

• Catching up with you about the Serverless Rag-Tag-Gang, next time it really is my shout

• I was really looking forward to a redbowl selfie this time

• I wanted to meet Mr Purple

• Go hunting for a nice steak with you at 10pm and cursing why we didn’t leave the party earlier so we can go find a greater steak

• Plotting with you over how we’d distract the Bellevue MS Store employee and run off with the first Microsoft Studio we saw there.

• Accidentally yelling my introduction into the middle of a PnP monthly podcast recording because really I’m just a walking disaster waiting to make a fool of myself. And it turns out you all knew me anyway so there was no need to yell like that.

• Impersonating a vendor employee while taking a group photo with you.

• Discussing merits of bacon with you, and how we can always add more bacon salt to bacon.

• Not knowing how to use a mic to ask a question in a full room full of people.

• Sneaking out the back to get a thickshake with you.

You are one of a kind. You came from all over the world. We don’t even have the same native languages, but we are the same. The same kindred spirit. There’s dozens and hundreds of us. It is such a peculiar sight.

I hope to see you all instead in 2020.

On using Microsoft Flow as a pre-ETL step for Power BI

Photo by WeRoad on Unsplash

A topic I’ve presented a few times to an Power BI crew is the concept that Microsoft Flow makes a great pre-ETL step for Power BI.

I talked to many attendees at Difinity Power BI Conference Auckland about this. It’s probably a time to write this up to summarize my thoughts.

There’s just something nice about having a product called Flow gathering data into your lakes.

Power BI

Power BI - Power Query and DAX is extremely good at crunching numbers.

Flow has variables, branching and loop logic, but they are slow for number crunching. Loop limitations like 5k rows or total number of action steps are also very limiting.

Don’t use Flow to crunch numbers.

Flow

Power BI connects to about 70 sources - Flow connects to about 250 connections.

Flow’s HTTP action and Custom Connector framework is better at gathering data. Automatic paging, more authentication options, retry policy, async HTTP request.

Flow can gather data on Trigger, copy them all to a simple location (e.g. SharePoint library or Azure Blog Storage) for Power BI to start refresh. So data processing can be done in a secure way.

Flow can call Power BI API to start a refresh. Turning the ETL into a trigger-push system. We get the best of both worlds - Flow to gather data, and Power BI to crunch them.

Power BI Scheduled Refresh with Flow

1. Schedule Refresh requires Flow to call Power BI API with a HTTP Request. But until very recently – this requires a delegate permission (so Flow has to call Power BI as a user) to perform delegate permission call requires a Custom Connector – which is what this blog post from Konstantinos goes into detail about creating.
   
   https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33
   
   

2. In February, Power BI has started releasing application permission to call Power BI API – this work is still in preview and requires quite a lot of set up, it also requires the administrator to approve the permission. App-only read/write everything is a pretty high level permission, non-admin can't grant this.
   
   https://powerbi.microsoft.com/en-us/blog/use-power-bi-api-with-service-principal-preview/
   
   pros: easier to call with Service Identity, and HTTP request
   cons: may be impossible to get your admin to approve a new AAD APP permission
   

3. The April roadmap says Refresh is an action coming to Flow’s Power BI Connector – this will use delegate permissions and would be much simpler to use. So my thinking is that wait for this to drop after April
   
   https://docs.microsoft.com/en-us/business-applications-release-notes/April19/microsoft-flow/improved-power-bi-connector

#FlowNinja hack 78 - modifying Modified By and Modified time with Microsoft Flow

In this quick blog post - we talk about how we can tweak the “Modified By” user field and “Modified” datetime field in SharePoint list and document libraries using Microsoft Flow.

This technique follows the very detailed article by MVP Andrew Koltyakov on LinkedIn - he describes how to call it with C#, JSOM and REST. He also has detailed examples of every possible field and what format you’ll need to run this with.

I’m calling it with Flow. So to see the full explanation you’ll have to read Andrew’s article ;-)

Also, before you read any further - please repeat the title of the blog post loudly, really fast 3 times.

Plan

• Requirements - why do we want this?

• Call ValidateUpdateListItem method with Flow

Requirements - why do we want this?

• You have updated a list item - you don’t really want the items to all say “Updated by System Account”.

• You’ve copied a bunch of files but you don’t want it to all say “Modified (now)”.

• You want to modify the item but you don’t want to create a new version.

• You uploaded a file but you don’t want to have two versions when you tweak the metadata.

• You wanted the update to tell you if the validation fails some SharePoint list item rule (this method will return a validation array object)

I need this. You also need this.

I don’t think we need to explain why you really really need this.

Flow

Here we have an item - modified a few seconds ago by me.

View fullsize

We want to update two values - the Modified By and the Modified (date time) fields.

Note the User is a JSON object that needs to be serialized by string() inside another JSON object.

The modified date is a date value, formatted to the “g” format. This format is usually MM/dd/yyyy HH:MM AA in EN locales. It is DIFFERENT if your environment has different locales. You can use “g” as date time string format to get the correct one for your locale.

Updates: 2020-06

Instead of “g”, we can also use 'yyyy-MM-dd hh:mm:ss' which seems to work for all locales. More reading on this issue.

Also, Mikael pointed out we should use bNewDocumentUpdate: true to avoid creating a new version.

View fullsize

Here is when it runs. Notice the Editor field is escaped JSON string.

View fullsize

Result

The modified date is now Feb 18, 2018!

And the user is someone else - my minion Gandalf.

View fullsize

Extra Date Time Note

Here is an example of using addDays(utcNow(), -365, ‘g’)
So this date is a year before right now, with the ‘g’ date formatting string.

Resources

• This blog post was #FlowNinja hack 78 https://twitter.com/johnnliu/status/1097445738483576833

• https://docs.microsoft.com/en-us/previous-versions/office/jj171078(v=office.15)

• https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

Difinity Conference 2019 Auckland - Hackathon Workshop, Flow and PowerApps

I’ll be presenting at Difinity Conference 2019 Auckland on several presentations.

Difinity is the largest Microsoft Data Platform conference in New Zealand. I’ll be presenting two talks - one on Microsoft Flow and one on PowerApps.

Pre-Conf Hackathon/Workshop: Master your inner Flow and PowerApps

This is a whole day workshop covering PowerApps and Microsoft Flow at a beginner to intermediate level, for the Difinity conference - the exercises are tuned for PowerBI integration.

http://difinity.co.nz/pre-conference-workshops-difinity-2019/#CustomConnector

Introduction to PowerApps and Power BI

This session is about two things – it is firstly a thorough introduction about PowerApps, where it came from and where is it going.

And it is also about how the sum is more than the individual parts – when we combine PowerApps and Power BI we can build some truly amazing more interactive reports & dashboards.

How to make everything with Microsoft Flow (advanced)

This is a intermediate-advance level Microsoft Flow session that looks at the very different types of Flow automations that we can do. Whether it is personal automation, enterprise workflows, or developer webservices. Flow is that flexible tool.

Chat!

Those that have met me will know I’m a very chatty person!

I’ll be hanging out at the community booths, so come find me, or tweet me and ask your Flow, PowerApps (or SharePoint) questions!

MS Ignite the Tour 2019 Sydney - MS Flow x2

I’ll be presenting at Microsoft Ignite the Tour 2019, Sydney on two Microsoft Flow presentations.

Advancing the Flow - understand expressions in Microsoft Flow

Is a short 15 minute theatre session on expressions. In a short 15 minutes I’d like to cover why you might want to use expressions, and how it opens the entire Flow engine to your command.

Flow for Developers - insane low-code Serverless automation

This is a full hour breakout session where I wanted to talk about what we can do with Flow, at the intermediate to advanced end and the power it opens up to every platform it touches. Whether it is SharePoint Online, Dynamics CRM, PowerApps or Power BI, from Microsoft 365 to Dynamics 365 to Azure. We have 250+ connectors and it is completely bananas for developer productivity.

Chat!

Those that have met me will know I’m a very chatty person!

I’ll be hanging out at the community booths or at the SharePoint Gurus + Valo booth, so come find me, or tweet me and ask your Flow (or SharePoint) questions!