Using the Power Function.InvokeAfter to determine how long to wait between API calls

I have been working on a dataset which I will hopefully reveal soon, but part of that was that I was getting rate limited when making an API call.

I found Chris Webb’s insightful blog post (Using Function.InvokeAfter() In Power Query) where he details how to use the Function.InvokeAfter. The one key piece that I personally found missing was how to use this with an existing function that I had created.

I then got another fantastic question from the Power BI Community where they were looking to do an IP Address Lookup. And there are a lot of sites who offer this, but they do limit the rate at which you can query the API (Which I think is perfectly understandable considering they are offering it for free!)

My blog post shows how I ensured that I did not exceed the rate limit for the API using the Function.InvokeAfter

NOTE: I am not going to cover how I converted the IP Address to a location, I have done this previously in (Power BI Query Editor – Getting IP Address Details from IP Address)

Using the Custom Function

I am starting off where I have already created the Custom Function in Power Query Editor. I also have got a table with IP Addresses.

  • I have used a sample file in which I made up the IP Addresses as shown below.
  • I then went into the Add Column in the Ribbon and clicked on Invoke Custom Function
  • This brings up the Invoke Custom Function
    window and I put in the following information as shown below.
    • As you can see from above, I gave my new column a name of Details
    • I then clicked the drop down and selected my function I created earlier called fx_GetIPAddressDetails
    • And then finally the crucial
      part is where I selected
      my IP Address Column.
    • I then clicked Ok.
  • When you do this it returns a table as shown below.
  • Click on the Expand
    Table Button on the top right hand side, which will then prompt you which columns you want to select
  • I then selected Country
  • And below were my results.

Adding in the Function.InvokeAfter to limit the rate at which I query the API

I am now going to modify the step where I Invoke the Custom Function to limit how long it waits between API calls.

  • I created a new Parameter called “Interval (Secs) as shown below.
  • I then went to the following step in my table, and clicked on the Step called “Invoked Custom Function”
  • Then in the formula bar I have got the following.

= Table.AddColumn(#”Changed Type”, “fx_GetIPAddressDetails”, each fx_GetIPAddressDetails([IP Address]))

  • Next, I made the following changes to the above code using the Function.InvokeAfter

    = Table.AddColumn(#”Changed Type”, “fx_GetIPAddressDetails”, each Function.InvokeAfter(()=>fx_GetIPAddressDetails([IP Address]), #duration(0,0,0,#”Interval (Secs)”)))

  • I added the Function.InvokeAfter(()=>
    before I called my function fx_GetIPAddressDetails which is highlighted in BLUE above
  • I then put in the #duration(0,0,0,#”Interval (Secs)”))) which is highlighted in PURPLE above.
    • Within the #duration I also used my Parameter called #”Interval (Secs)”
      which is highlighted in ORANGE.
    • This allowed me the flexibility to change the rate limit timing without having to go into the code.
  • Now when I refreshed the data it will wait 2 seconds between each API Call.

I hope that this has been useful and an easier way to ensure that you can limit how quickly you call an API

As always please leave any comments in the area below.

Here is a copy to the PBIX file that I used in the blog post above: FourMoo – Loading IP Addresses with an Interval.pbix

Power Query Pattern – Adding Spaces in Text within your data with Camel Case

In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column.

To get this to work for you, all that you do is need to make one change to the code.

Below is what the data looked like

Then I created the following Power Query Pattern below.

#"TRANSFORM - Camel Case" =
Table.TransformColumns(
#"Removed Columns3",
{{"Operation",
each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " "), type text}})

 

  • To use this pattern below are the following changes that you will need to make it work in your Power Query Editor
  • Line 1
    • This is my step name
  • Line 2
    • This is where I am using the Table.TransformColumns
    • NOTE: Even though this does appear to only be used for transforming columns, it works for data within a column.
  • Line 3
    • This is referring to the previous step name, which is returning the table contents
  • Line 4
    • This is my column name where I want to add in the spaces. As with my image above the column was named “Operation”
    • NOTE: This is the only part of the pattern that needs to be changed.
  • Line 5

What I did to get this to work is I was in my Power Query Editor Window

I then clicked on Advanced Editor in the Home ribbon

I then added this step into my code as shown below.

I then clicked on Done

I went back to my column and I could now see the data with the spaces after each capital word

Conclusion

As you can see I have demonstrated how Powerful Power Query (see the multiple use of Power!) is to get the data in the shape that you require.

If you have any suggestions or comments please let me know.

Power Query – Renaming Multiple Columns

I was working on a dataset where I wanted to change multiple column names using one step and not having to change them manually. Since there were over 30 columns this would be really time consuming.

Below I detail how to complete this in the Power Query Editor, which will replace all the column names for me in one step.

This once again shows how powerful the Power Query Editor can be for ETL and automation tasks!

  • This is what the Original Column names looked like
  • To get this working, I had to go into the Advanced Editor and manually add a step.
  • Here is the Syntax that I added with my Step Name

    #”Rename Column Names” = Table.TransformColumnNames(#”Changed Type”, (columnName as text) as text => Text.Replace(columnName, “Sales – “, “”))

  • As you can see above my previous step name was called #”Changed Type”
  • Then the only other change I had to make was for the Text.Replace and what I was searching for and what I wanted to replace it with.
    • With this example I was looking for “Sales – “, “”
      • As you can see I searched for Sales – and replaced with “” (which is blank).
  • And this is what it looked like after manually adding in the above step.

As I have shown above a quick and easy way to rename multiple columns at once.

You can find the sample file here: Renaming Multiple Columns.pbix

As with every blog post if there are any comments or suggestions please leave them in the section below.

BI-RoundUp – Power BI (On-Premise Gateway Update – Developer Community Update)

Here is this week’s BI-RoundUp. I am hopeful that by this time next week the latest version of Power BI Desktop will be released!

Power BI – On-Premise Gateway Update

By far the biggest news is the capability to use Custom Data Connectors in the Personal Versions of the On-Premise Data Gateway. The Power BI Team did indicate in the blog post that support in the Enterprise version will be coming in a few months. This is fantastic to see it being made available.

There now is single sign on support using Kerberos for SAP Business Warehouse Server

And finally, there is the updated version of the Mashup Engine to the April version of Power BI Desktop

Here is a link to the blog post: On-premises data gateway April update is now available

Power BI – Developer Community Update

As you can see above for the Developer community update there now is the capability to leverage Custom Report Tooltips and the Q&A explorer.

There is now also new Azure Resource Metrics which allows for better metrics to understand how your Power BI Embedded application is working with the following metrics.

  • It will show Memory usage being used
  • It will show when there is Memory Thrashing, which is defined as when a report is trying to be run, and it needs to evict another dataset in order for your dataset to be loaded into memory. And this only applies to imported datasets.
  • It will show QPU High Utilization which shows the Query Processing units every minute on your resources when it is about 80%

There is now the capability to set up alerts on your Azure resource.

Finally, there is a new learning channel to create custom visuals with a hands-on lab.

All the details of the blog post are here: Power BI Developer community April update

BI RoundUp – Power BI (March Desktop Update, Share Content with anyone via email, Persistent Filters, Mixed Reality App, Service & Mobile Summary for Feb, Data & BI Summit)

After getting back from the MVP Summit in Bellevue, which was an incredible experience the Power BI team has been extremely busy getting some great new features out the door.

Which means there is quite a lot to cover in this week’s BI-RoundUp.

Power BI – March Desktop Update

Once again, I must repeat what I have said before, the Power BI team has once again released some amazing new features. Which I think is continuing to make Power BI such a compelling and amazing product to not only use, but to show potential customers.

As I do with every month’s update, I will highlight the features which I feel are worth mentioning.

Reporting

Without a doubt the Report Tooltips is a game changer. Not only does it give an almost drill through type of feature, but it is context aware. So, when you enable the tooltip it will take into account all the filters that are being applied and apply that to the tooltip. This allows users to gain more insights without having to navigate to another page.

Bookmarking is now generally available, which means that it is now fully supported. As well as you will no longer need to go into the Preview Features in Power BI desktop to enable the feature.

Additional formatting improvements for this month is for the table and matrix, where you can now modify the display units to show it in a more meaningful way. As well as you can set the precision in terms of decimal points. One thing to note is that it will override the settings that were defined in your model.

You can now turn off the Visual headers for the entire report. This is particularly useful when you want to embed the reports. This will remove the Context Menu’s, the Focus Mode, drill down, and the option to pin the visuals. This could potentially also make the pages a read only style report.

When you are now adding in new visuals into your Power BI report, it will now look at the existing visuals and place it where it thinks it should logically go. This is great if there are already a few visuals where it can save you some time placing the visual where you wanted it to go.

Custom Visuals

There are a whole host of new Custom Visuals which are detailed below.


Mapbox visuals which have a lot of amazing options for viewing maps

User List by CloudScope

Timeline by CloudScope – Similar to a Twitter like feel

KPI Chart by Akvelon

R DataTable

Outliers Detection

Data Insights by MAQ Software

Dumbbell Chart by MAQ Software

Clustering using OPTICS by MAQ Software

Data Connectivity

There have been improvements to the SAP HANA connector.

Both the SAP BW Direct Query and Azure Analysis Services are now generally available.

Other

The error reporting has now been integrated with Windows Error reporting. What this means is that it now will contain more information. You can also view all your previous errors. As well as if you do not have the time to send the error report right away you can send it later.

You can find all the detailed information in the blog post: Power BI Desktop March Feature Summary

Power BI – Share Content with anyone via email

You can now Share your Dashboards, Reports or Apps with any email address including Outlook.com (Live.com, Hotmail.com) and Gmail. This is really great when you have users or organizations that use the public hosted email addresses.

One word of caution, is to ensure that only the people who are required to send emails externally have the requirements. And other people that do not require it, have it disabled.

You can find all the details on how to share here: Share your Power BI content with anyone by email

Power BI – Persistent Filters

One of the time-consuming tasks in the past was, when you went back to a report, if there were multiple slicers and filters, you would have to reset them each and every time you went back to the report.

This is now a thing of the past with persistent filters.

Persistent filters will now remember your Filters, Slicers, Drill downs and sort order for your reports. So, when you go back to the report it will remember them.

This is a great feature and will make the reporting and analytics experience that much more enjoyable and quick to gain the insights you require.

All the details are here: Announcing Persistent Filters in the Power BI Service

Power BI – Mixed Reality App

With the use of the HaloLens and the Mixed Reality App there now is the capability to be able to Pin Power BI reports to physical locations or to have it tagged within the App.

What this means is that if there is a user on the factory floor, they can have a Power BI report pinned into the machine, so without having to look at their phone or open a laptop they can gain insights as they are moving around.

I think that this opens a whole new avenue and the things that you can imagine are almost endless.

The blog post details are here: Power BI for Mixed Reality app now available in Preview

Power BI – Service & Mobile Summary for Feb

Whilst some of the new features have been covered before here is a quick overview.

There is now the facility to automatically install apps.

A new feature that I was not aware of is now additional
Power BI Premium Capacities for P4 and P5, currently only available in West US and Southeast Australia.

Organizational Custom Visuals can now be controlled by the Power BI Admin

The February update to the On-Premise Data Gateway which has the latest Mashup Engine, and can also refresh data sources from both On-Premise and Cloud.

And finally, updates to the mobile app where you can now share from the Mobile App, you can drag your finger across your screen to get the tooltips and improved hyperlinks

All the details are here: Power BI Service and Mobile February Feature Summary

Data & BI Summit in Ireland

The Data & BI Summit in Ireland will be happening from 24 – 26 April 2018.

They have recently announced the keynote speaker John Doyle, Senior Director of Product Marketing on the Cloud & Enterprise team at Microsoft.

This looks to be a great conference and would be well worth attending if you are able to.

All the Summit details are in this blog post: Data & BI Summit announces keynote: The Next Era of Analytics with Microsoft Power BI

Power BI – Finding the data type when using Variables in Measures

I have been working with an Organization where I have had to create some rather complex measures. And within these measures I am using a fair number of variables.

I found that at times it was a challenge to understand what data type was being returned by the variable. Which at times would lead to a different result, because the Variable data type being returned was different from what was expected.

Example

For example, in my measure I wanted to get a date range, if the variable was not a date or date/time data type then my measure would fail.

So below I will show you how to easily find out what the Variable data type is.

Last Year Sales = 
VAR Date_MaxDate =
    MAX ( 'Orders'[Order Date] )
VAR Number_TotalDiscount =
    SUM ( 'Orders'[Discount] )
VAR Text_CustomerName =
    LASTNONBLANK ( 'Orders'[Customer Name], Number_TotalDiscount )
RETURN
    Date_MaxDate

 

  • Above is my measure and as it currently stands it will return the value for the variable Date_MaxDate which is created on lines 2 & 3.
  • Now I selected the measure name [Last Year Sales] under fields in Power BI Desktop.
  • I then clicked on Modeling in the ribbon and under Formatting it shows me that the data type is Date Time, as shown below.
  • The data type for my Variable called Date_MaxDate is Date Time.
  • If I now changed the result to be Number_TotalDiscount on line 9, it then shows me that the data type could be any numerical format
  • And finally, if I change the result to be Text_CustomerName on line 9, it then shows me that the data type is Text

Conclusion

As I have shown in my blog post above, a quick and easy way to see what data type is being returned by the variable.

If you have any comments or questions please leave them in the section below.

Power Query – How to get same row number for x number of rows

I was answering a question on the Power BI Community Forum where a user was looking to get some data from an Excel spreadsheet into Power BI. And in order for me to complete it, I wanted to get the same row number for x number of rows.

Example

  • I had some sample data where the same data was repeated every 7 rows
  • What I wanted is that for every 7 rows the Index number to be the same.

(more…)

BI-NSIGHT – Power BI (Community Blog Highlights – Power BI Germany – Latest Updates for Power BI Desktop – How Microsoft uses Power BI to run its Cloud Business – Connecting Datasets to Power BI Service) – SQL Saturday Brisbane (I’m presenting) – SQL Server (SQL Server 2017 – DAX Editor for SSDT – New Get Data Experience for SSDT)

I thought that this might be a quieter week, but once again there are a whole host of updates from Power BI updates, to SQL Server 2017 announcements. Quite a bit to read through so I hope you enjoy it.

Power BI – Community Blog Highlights

I actively participate in the Power BI Community, as well as when I feel like I have something meaningful that will also contribute to the Power BI Community get it published onto the Power BI Community Blog. And in this month’s highlights I am thrilled to have my latest blog post as part of the highlight.

The other posts are below:

Power BI – Infographic for Power BI from an end to end perspective, by Gilbert Quevauvilliers

Design Pattern – Groups and Super Groups!, by Greg Deckler

Get Your Own Power Query Editor using Notepad++, by Lars Schreiber

What I Learnt Building Power BI Custom Visuals, by Chamara Ranasinghe

A Simple and Fun Guide to Microsoft Flow and Power BI, by Ruth Pozuelo

The Ultimate Waterfall Chart , by Klaus Birringer

Power BI – Germany

I know from personal experience that having your data reside in the same country you are working from means a much easier and broader adoption of Power BI. Now for people living in Germany they are able to fully comply with all the requirements in terms of having data hosted within Power BI.

As a side note I also saw that Microsoft is the first company to offer a data centre in South Korea which is great to see.

You can find the blog post details here: Experience your data with Power BI Germany and meet your compliance and regulatory needs

Power BI – Latest Updates for Power BI Desktop

I recently came across this link below, which always will point to the latest details with regards to the Power BI Desktop.

I would suggest bookmarking this page, to ensure you always have the latest information available.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-latest-update/

Power BI – How Microsoft uses Power BI to run its Cloud Business

This is a really interesting blog post where they details how Microsoft uses Power BI to monitor Power BI as well as all the other BI components that fall under James Phillips. What amazes me is that for each of the KPIs shown in the screen there is a General Manager responsible for that particular product.

What this also shows is how to build an effective KPI dashboard which is easy to view and know when something is not going as it should.

You can find the interesting read here: How Microsoft uses Power BI to run its growing cloud business

Power BI – Connecting to the dataset in the Power BI Service

In this blog post from the Power BI team they go into some details around how and why you would want to use the Power BI Dataset in the Power BI Service.

One thing that I found interesting and I guess I had never thought of is that you can now connect to ANY dataset that has been published to the Power BI Service. This includes datasets from Curated Content Packs which makes the development experience that much easier.

You can find the details here: Connecting to datasets in the Power BI service from Desktop

SQL Saturday Brisbane – I’m Presenting on Power BI Real World Tips and Tricks

I am so excited that I will presenting for the first time at SQL Saturday Brisbane on Saturday 27 May 2017.

The session details are Power BI Real world tips and tricks. Below is the session excerpt:

Out in the real world, people are starting to use Power BI to gain insights into their data that were previously never seen. With Gilbert currently working full time in Power BI projects, he has first hand experience on how to leverage Power BI in work place.

By providing real world, real working examples I plan to show how some simple tips and tricks can make their reports and dashboards amazing and insightful. This will be an interactive and demo rich presentation.

The real world tips will cover the following sections of Power BI:

  • Query Editor
  • DAX
  • Visuals
  • Power BI Service

You can register for SQL Saturday here: SQL Saturday Brisbane – 27 May 2017

SQL Server – SQL Server 2017

I personally think that with the release date of SQL Server 2017, that Microsoft is looking to continue what it has been doing with the combination of experience with Power BI and deploying updates to Azure SQL Server.

This is allowing them to deploy and implement changes to SQL Server in at a faster pace. I do also personally think that with the deployment and testing with Azure, they can test real world scenario’s with a diverse environment to make sure that it works as expected. This allows them to then take all these learnings and put it into an On-Premise Solution.

You can find the details here: SQL Server 2017

SQL Server – DAX Editor for SSDT

It was just a matter of time before the DAX editor became part of SSDT (SQL Server Data Tools) and now it is finally here.

This will no doubt make the creation and editing of measures and calculated columns a lot easier and quicker to develop.

They are planning to have Intellisense as well as Code Formatting in future releases

You can find the details here: Introducing a DAX Editor Tool Window for SSDT Tabular

SQL Server – What’s new in SQL Server 2017 for Analysis Services

As you can see above there have been a whole host of enhancements for Analysis Services in SQL Server 2017.

The new Object-Level Security to secure more data looks really interesting in that you can now also secure it on a column level. As well as performance improvements for the developer experience, which I personally know in the past at times was particularly slow.

Along with improvements to the DMV’s, similar Date Hierarchy implementations that are currently in Power BI.

As well as other improvements you can read about here: What’s new in SQL Server 2017 CTP 2.0 for Analysis Services

SQL
Server – New Get Data Experience

With the new release of SSDT they have updated the Get Data Experience for SSAS models with level 1400.

It appears that they are looking to align with what is currently being implemented in Power BI, which is fantastic to see because it will mean a more consistent developer experience.

They are continuing to add more data sources with each release.

You can find all the details here: New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

BI-NSIGHT – Power BI (Desktop Update April – Quick Measures Preview – Mobile Apps Summary – Video Gallery & Ask a Partner Anything – View Related Content Pane – Sneak Peak add new Columns from Example – Tracking Adoption via Power BI Audit Logs)

There has been a whole host of updates and interesting things in Business Intelligence in the past weeks, so please find below the updates.

Power BI – Desktop Update April

This month’s Power BI Desktop update for April has a whole host of great updates and new features this month, which I will highlight what I think is most relevant below.

Report View

The first update in the Report View section is the ability to rename the axis titles. I know that personally this is something that I have been looking for and now the capability is there.

Next is additional updates to the Matrix Preview, in which you can now sort the matrix by the grand total and row headers in ascending or descending order.

As well as now you can also resize the columns as you could do previously in the original matrix.

And finally, you can now word wrap column headers, row headers and values in the matrix preview. This can be configured separately in the formatting pane.

Analytics

Not only has the Power BI team made it better and easier to create the default measures. But with the Quick measures they have made it as simple as a drag and drop and then Power BI Desktop writes the DAX for you. I personally think that is a giant leap forward as it enables users who are not very strong in DAX to still be able to create powerful measures, which in turn will create greater insights into their data. This will also then let people learn how DAX works because they can see and modify the code, as well as give more advanced DAX users the capability to edit the DAX measure if so desired.

This entire preview feature I think is another game changer and it will enable people to quickly create the measures that they require, as well as to get people to build their DAX skills if so required. And I have no doubt that more quick measures will be added.

As you can see above, you can now try Q&A in Spanish. This must be the result of the Power BI Surveys that have been conducted in the past.

Data Connectivity

Once again this is another idea that a lot of people have been asking for and it has already been delivered. This is the ability to connect to a dataset that exists in the Power BI Service.

This is wonderful as what it means is that as long as you have got the required access, you are now able to access a dataset already uploaded into the Power BI Service. So what this also means is that you will not have to worry about creating measures, how the data is updated etc, because you are a consumer of the Power BI dataset.

This also means that now you can have multiple people working on a single dataset. Which is the start of having multiple developers developing reports and insights into your data.

The one caveat is that when you use the Power BI Service dataset you cannot edit or modify anything within this dataset, you will have to get the dataset owner to make the required changes and then for them to upload it the changes or updates into the Power BI Service. But still a giant leap forward.

The Amazon Redshift data connector has now moved out of Preview and is in the Beta phase, so it is easier to find and use.

There has also been updates to the SAP Hana and BW connectors, now giving you the ability to have more control with regards to the parameters selection. I am sure that people that use SAP will find this very welcome.

Query Editing

The ability to add a column by example has been out for almost 2 weeks now, but this is once again (yes I am repeating myself a bit!) a game changer because it now allows people who do not understand Power Query or the M language to quickly and easily build new columns within their dataset. And this also does it in the Query Editor, where I personally think it is the right place for this to be done, so that before it is brought into the Power BI Desktop Model, the data is already there and can achieve better results.

As you can now see above you now can split your column by delimiter or number of rows and if you wanted to split it into Rows.

The basic Group By in the Query Editor now allows you to group by a single column and output by a single column.

The Go to Column is a very handy feature because it allows you to quickly find your column, which is especially helpful when your table is very wide with a lot of columns.

You can find the blog post for the Power BI Desktop Update here: Power BI Desktop April Feature Summary

Power BI – Quick Measures Preview

There is already a blog post out by the Power BI team with regards on how to leverage and use the Quick Measures Preview.

It has some great content and is well worth the read.

You can find the blog details here: Quick Measures Preview

Power BI – Mobile Apps Summary

There have been some additional updates to the Mobile Apps for Power BI which are the Q&A Improvements and 3D touch for iOS.

Multiple SSRS Server support for up to 5 SSRS Servers. As well as improvements to the slicers.

You can find all the blog details here: Power BI Mobile apps feature summary – March 2017

Power BI – Video Gallery & Ask a Partner Anything


As you can see from above the Power BI team has now launched the Video Gallery where you can view tips and tricks on how to do things with Power BI. I think that this is great because it is often easier to see how to do things.

As well as another Ask a Partner anything on 06 April.

You can find all the details here: Announcing the new Power BI Video Gallery and Ask a Partner Anything live event

Power BI – View Related Content Pane

Once again the people in the Power BI team, specifically on the Power BI Service side are making it a lot easier to navigate and understand how your reports piece together.

This is very powerful as well as easy to use, because you get to see all the related content, but you also get the capability to be able to go into the settings or item specific areas, where before you would have had to go through a few clicks to get there.

You can find the blog details here: Announcing the View Related Content Pane: Faster Results with Fewer Clicks

Power BI – Sneak Peak add new Columns from Example

As you can see from above, this is a sneak peak of a new feature that is going to be released in the next version of Power BI Desktop.

I have to say that this is amazing, as it is starting to do the hard yards for you. Yes it might take a bit of practice to get it right, but once you have mastered it, it can save you a lot of time and effort

And the thing that I really like is that it will enable the users who are not so proficient at Power Query to be able to expand on their data, which in turn will lead to amazing insights.

You can read the blog post here: A sneak preview of the new Add Column From Examples data transformation

Power BI – Tracking Adoption via Power BI Audit Logs

This is a great blog post from JAVIER GUILLEN, where he shows you how to use the Power BI Audit logs to show how adoption of not only Power BI, but also the dashboards and reports are being used.

You can find the blog details here: TRACKING ADOPTION VIA POWER BI AUDIT LOGS

SSAS / Power BI – DirectQuery WhitePaper

As you can see above there is a whitepaper for DirectQuery in SQL Server 2016 Analysis Services, it is noted that some of the concepts are shared with Power BI.

And I have already downloaded the whitepaper and will be getting stuck into it very shortly.

You can find the blog post details as well as the link here: DirectQuery in SQL Server 2016 Analysis Services whitepaper

BI-NSIGHT – Power BI (Granular Tenant settings, Amazon Redshift connectivity in Power BI Service, Featured Data Stories, Availability Monitoring Publish to Web) – Excel (Get & Transform updates) – SQL Server (DAX support in Report Builder and SSMS)

Here are the weekly updates.

Power BI – Granular Tenant Settings

I can personally say that I know a few of my clients who are very happy that the Power BI team has already implemented the Granular Tenant settings for Power BI. I have to say that I am once again impressed by the Power BI Team really a great job.

I also like the fact that they have gone one step further and put in the option for “Except specific security groups”, because so often you want to allow it for almost everyone, except one group. And with their implementation they make this really easy and simple to implement.

Currently as at 16 Mar 2017, the granular settings are for the following:

  • Export and sharing settings
    • Share content to external users
    • Export Data
    • Export reports to PowerPoint presentations
    • Print dashboards and reports
  • Content pack settings
    • Publish content packs to the entire organization
    • Create template organizational content packs
  • Integration settings
    • Use Analyze in Excel with on-premise datasets

You can read the blog post here: Announcing granular tenant settings in Power BI

Power BI – Gateway Update for March

As you can see above the Gateway for Power BI has had a whole host of updates in the March 2017 release.

In this release there is now an updated UI (User Interface), where you can now see the status of the Gateway.

You also have the options under the Service settings to restart the service, as well as change the Gateway Service Account.

In the Diagnostics section, you can now enable verbose logging, as well as export all the gateway logs to one zip file.

And finally under the Network section there is the capability to view the Local network status, as well as modify the Azure Service Bus connectivity mode.

There is also an update for the Gateway to use a FQDN instead of a list of IP addresses so that you will no longer be required to keep a list of IP Addresses.

Also if you are looking to use an Oracle data source with Kerberos configured the Power BI Gateway team are looking for candidates.

You can find the blog post details here: Power BI Gateways–March Update

Power BI – Amazon Redshift in Power BI Service

This is a informative blog post by the Power BI team where they explain how to connect, and publish your Power BI reports using Amazon Redshift.

You can find the details here: Announcing support for Amazon Redshift connectivity in the Power BI service

Power BI – Featured Data Stories

Congratulations to David Eldersveld on his featured data story, really a great way showcasing Power BI as well as the other people featured.

Next month’s feature requests are tables and matrices.

You can find the featured data stories here: Congratulations to this month’s Featured Data Stories

Excel – Get & Transform Updates

There are two updates to the Excel Get & Transform updates in the March 2017 update. The first is a new transformation which allows you to extract data values from a column. And the second is where you now have support for SQL Server Failovers when using a SQL Server database connection.

You can find the details here: March 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Availability Monitoring Publish to Web

Charles Sterling from the Power BI team has a great blog post if you want to monitor the availability and ensure that your Publish to Web is working. He shows this in a few simple steps.

You can find the details here: Setting up availability monitoring for Power BI Publish to Web Results

SQL Server – DAX support in Report Builder and SSMS

In the latest release of Report Builder and SSMS (SQL Server Management Studio) there now is the ability in the query designer for DAX.

You can find all the details here: Query designer support for DAX now available in Report Builder and SQL Server Data Tools