Goodbye Lightswitch – Hello PowerApps?

Nur so am Rande bemerkt, dass war auch mal ein Projekt von Microsoft, welches mir sehr vielversprechend schien. Nur wurde es über die Jahre immer ruhiger und ruhiger und es war klar, dass es früher oder später wohl aufgelassen wird. Wie es scheint, sind aber ne Menge Leute ganz schön angepi…. Durchaus verständlich

LightSwitch Update

Solving Real World Scenarios with Microsoft Flow – Challenge No. 2

This is the second challenge:

+ Periodically get currency exchange rates from a web service

+ Insert the rows in a staging table (SQL Database On-premise)

+ Parse inserted Json Data with a stored procedure and insert results in our final Currency Exchange Rates Table.

The resulting Flow would look like this:


Let’s get things started:

HTTP: I’m using to get my data. No Problem so far.

Insert Row: I have installed and configured the On-premises data gateway and it’s working.

Execute stored procedure: Here comes trouble:


Sadly up to now stored procedures are no supported when using a gateway. But hopefully we get this feature soon as stated here.

I then figured out the following workaround. Create an AfterInsert Trigger on the staging table to transfer my data to the final table. But I had now luck:


After some investigation, I found this thread. Currently you can’t use the Insert Row action on a table with an existing trigger.

So, this is really bad. At present neither stored procedures nor triggers are supported when dealing with Microsoft SQL Databases via the On-premises data gateway. Challenge lost at least for the moment.


Solving Real World Scenarios with Microsoft Flow

This is my first post in English. The reason is I’m using an API named EU VAT API provided by chema.

Up to now for me Microsoft Flow was just one new hot thing offered by Microsoft. But this article has inspired me to challenge Flow with a real-world requirement.

One common task in Europe is to check the VAT Registration Number of a given company. You will find many solutions if you search the Internet. I myself have implemented solutions in VBA, SSIS and so on.

But can this be done in Microsoft Flow? The short answer is Yes. To be honest if you use the api provided by chema it is surprisingly easy after all. Here is my demo solution:

+ In Flow my starting trigger is “When a new email arrives”:

+ I’m checking if the body of the mail contains the phrase “*VAT*”:


+ If so I’m calling the API where subject is the address of the API + the subject of the mail received which in fact is the VAT Reg. No. to check:


In the last step the result of the API call is returned to the sender


+ The first mail looks like this:


+ And the return mail I receive almost immediately goes like this:


The flow in the result window looks like this:



No this was really very simple and absolutely no coding required. Challenge No. 1 won by Microsoft Flow…

Calling Microsoft Flow From Power Query And Power BI

Chris Webb's BI Blog

Since last week’s blog post caused quite a lot of interest, here’s something similar: did you know you can trigger a Flow in Microsoft Flowand get a response from it back using Power Query/Power BI?

To start off, I suggest you read this post by Irina Gorbach which shows how a Flow can be triggered by a call to a REST API. Now consider the following Flow which is similar to the one in that post:


It has a Request trigger:


…a Translate text step as the second step, that takes the text passed in to the Request trigger through the request body and passes it to the Microsoft Translator API where it is translated from English to French:


…and then, finally, returns the translated text back using a Response step:


It’s very easy to call this Flow from Power Query or Power BI. First, create two parameters in…

Ursprünglichen Post anzeigen 221 weitere Wörter

Automation of Excel Macros — Paul te Braak

Guter Tipp, starte oftmals Routinen von aussen und zeitgesteuert aus Access Datenbanken. Da bin ich auch schon mal in das geschilderte Problem mit den fehlenden Ordnern gelaufen…


There is still a huge value of reporting and using Excel for end users. Excel is simple to work with, allows data access and allows end users to program with minimal learning. As users develop their skills they inevitably look at automation through macros. So if we want to turn this into something a little […]

über Automation of Excel Macros — Paul te Braak

Power BI für Datenmodellierung

Sehr ausführlicher Beitrag zum Thema Datenmodellierung mit Power BI als Werkzeug:

Power BI for Data Modelling