Excel – Geography Data Type

Durch diesen Link bin ich auf den neuen Datentyp „Geography“ in Excel 365 aufmerksam geworden:


Um das auszutesten, habe ich mir schnell eine Liste der Gemeinden in meinem Bundesland besorgt und den Datentyp eingesetzt. Bis auf zwei Ausnahmen hat er alle Orte korrekt gefunden, das finde ich mal schon beeindruckend.

Das Resultat schnell in Power BI hochgeladen und schon habe ich eine „Map“ mit allen Orten und Informationen wie Bezirk oder Einwohnerzahl.

Und das alles in 30 Minuten – Fazit: Echt stark…


A few DAX functions

Data Warehousing, BI and Data Science

This query shows summarise, addcolumns, filter, union, row and format DAX functions:

  • The financials table is filtered on Product = Montana
  • Display just the Country column using Summarize
  • Sales column is added using AddColumns functions
  • Total row is added using Union function
  • Add commas for thousand separator using Format function

Canada 24,887,655
Germany 23,505,341
France 24,354,172
Mexico 20,949,352
USA 25,029,830
Total 118,726,350

Data source: link
Ref: Whytheq: link, SQLBI: link

Ursprünglichen Post anzeigen

Solving Real World Scenarios with Microsoft PowerApps

This is an alternative approach to solve the problem described in my blog post here:


This time I’m using PowerApps to „win“ the challenge. All of this is done in three easy steps:

Step 1:

For testing purposes, it is enough to sign up for a free plan here:


Step 2:

Then you need to set up a custom connector in PowerApps. I found the wizard very easy to use and after some trial and error I ended up with a working solution:

Step 3:

Last thing to do is setting up a new Canvas app. For my test I only needed a TextInput control to insert the VAT Number, a Button to call the api and a Gallery to show the results. Basically, all of this is up and running with only one single line of code:


If have to say I’m once again very impressed how easy it is to work with PowerApps. I was able to integrate an api service, create a custom connector and build a running demo solution in about 1 h. I must admit I found this article very helpful:


From here of course I will go on and enhance my little program. For example, I will store the results of the check in the SQL database. And if the check is returning false there should be another button to send an e-mail to my client asking for the correct VAT no. Or send a notification to someone responsible within my company.

Microsoft PowerApps Office Add-In enables user to read, modify and insert data in CDS environment. Add-In is available for download here for free, or you will be prompted to install Add-In for first time when you open entity data in excel from PowerApps portal. Add-In establishes connection to PowerApps OData service. Note: Add-In is compatible […]

über Manage CDS Entity Data – Microsoft PowerApps Office Add-In — Bansal Blogs – Dynamics 365, PowerApps, Microsoft Flows, Power BI

Calling The Power BI REST API From Microsoft Flow, Part 3: Finding All Data Sources Used In A Power BI Tenant

Chris Webb's BI Blog

[Part 1 of this series can be found here and part 2 here]

The problem with self-service BI is that you never quite know what your users are up to. For example, what data sources are they using? Are there hundreds of Excel files being used as data sources for reports that you don’t know about? If so, where are they? Could they and should they be replaced by a database or something else more robust? In this post I’ll show you how you can use Microsoft Flow and the Power BI REST API (see part 1 to find out how to create a Flow custom connector to call the Power BI API) to get the details of all the data sources used in all of the workspaces of your Power BI tenant.

I’ll admit that doing this turned out to be a bit trickier than I had expected…

Ursprünglichen Post anzeigen 717 weitere Wörter

Keeping Big Data Big with Aggregations


I have built several solutions over the years to enable business users to get at the data they need to do the analysis that is important to them. I would build cubes so that they could slice, dice and mold the data into the shape they need. They would use tools like Excel, Reporting Services and more recently Power BI to interactively work with an entire dataset. Then I entered the world of Big Data when I started building reporting solutions Microsoft’s Bing business teams. All the sudden, the data was too large to fit into one cube. Suddenly it became impractical to have to load all the data into one SQL databases.

While the size of the data presented many implementation challenges to the development team, it didn’t change what the business users needed to do with it. To enable them to use the same tools they were used…

Ursprünglichen Post anzeigen 939 weitere Wörter