Power BI – Quick Tips working with multiple large CSV Files

I have recently been working a lot with multiple larger type CSV files, and thought it would be good to share what I have found that makes the process run faster. I am always looking at ways to make sure that not only is the model efficient, but also the development experience.

Name your query first before applying steps in Query Editor

I have found that when working with csv files, if you rename your query in the Query Editor, it will then go and complete all the Applied Steps in your query. This in my case meant waiting a few minutes for a simple rename to take effect.

So always remember to give your query a name first, before doing any additional steps.

If possible combine all csv files from separate files into ONE file

I found that when using the Folder to import my CSV files, that when I put all the data into One file it loaded the data significantly faster.

Disabling Background Data in Options

When working with multiple tables that all load off the same CSV files, I found that Power BI desktop would start trying to refresh data in the background, and it would either make the Query Editor I was working on very slow. Or I would have to wait until the background data preview to complete. By turning this off it meant you only would refresh the data you are working on.

To disable this click on File, then Options and settings

Then click on Options

Then go down to the section called CURRENT FILE and click on Data Load

Now in the right hand pane under the section Background Data, remove the tick from Allow data preview to download data in the background. So that once complete it looks as shown below.

Then click Ok.

Now to ensure that it does take effect I would suggest closing and then opening Power BI Desktop.

BI-NIGHT – Power BI (Export to PowerPoint, New Visual Liquid Fill Gauge, Tech Tip Display Text, Automating Power BI Desktop Refresh, Webinar Create PowerApps to send real time data to Power BI) – DAX Studio (Release 2.5.0)

Another week closer to SQL Pass, and here are the recent updates and interesting articles

Power BI – Export to PowerPoint

It is great to see that after some screenshots from Microsoft Ignite that this has already been implemented into the Power BI Service. I am sure that there will be more details around this in due course from the Microsoft Power BI team.

I did export a Power BI report to PowerPoint and it exported each report to a different slide. And when you clicked on the slide it would then hyperlink you to the report within the Power BI Service. I am sure that this is the first iteration, and that the features will evolve over time.

You can find the blog post from Microsoft here: Export Power BI report to PowerPoint (Preview)

Power BI – New Visual Liquid Fill Gauge

As you can see from above there is a new custom visual, the Liquid Fill Gauge. And this is another really handy visual when you quickly want to show how your value is doing.

You can find the blog details here: Visual Awesomeness Unlocked: Liquid Fill Gauge

Power BI – Tech Tip to Display Text in Tooltips

From the people at Power BI, they have released a tech tip Thursday, with this week’s tip being how to display text as a tool tip as shown above. This can be really handy for when people are interacting and using the data.

You can find the tech tip details here: Tech Tip Thursday: Displaying text columns in tool tips

Power BI – Automating Power BI Desktop Refresh

There was an interesting blog post by Teo Lachev, in which he shows 2 different ways to refresh your Power BI Desktop File automatically.

The one option is to use a PowerShell script developed by Michal Dúbravčík (I am fairly certain that it works, but as always check the code when downloading from the internet), or the other option is to use Power Update to do this for you. Which is really easy and simple to use, as well as being extremely cost effective.

You can find the details here: Automating Power BI Desktop Refresh

Power BI – Create PowerApps to send real time Power BI

Hopefully people will see this in time, and if not I am fairly certain that they will publish the webinar later.

But please if you are interested and are using Power Apps, this looks like a great webinar to see how you can leverage Power Apps to send real time data to Power BI.

You can find details here: Webinar 10/20: Create PowerApps to send real time Power BI data

DAX Studio – Release 2.5.0

I have to say that anyone that is developing using Power BI, Power Pivot or SSAS Tabular then the DAX studio is most certainly a must have tool to have in your tool belt.

It offers a whole range of really useful and helpful ways to understand exactly what you are trying to achieve. And ensure that you do it in the most efficient way possible.

You can find the updates to DAX Studio here: DAX Studio 2.5.0 Release

Power BI Quick Tip – Using Parameters in Power Query Filters

I have been working lately quite a bit in Power Query and having to shape and re-create data based on the requirements.

They had a requirement to make the data dynamic and immediately the Parameters in Power BI came to mind to create the solution.

Now as we know using Parameters in Power BI has a lot of advantages.

But one quick tip that I want to share or highlight is that your parameters can be used in quite a few places in Power Query.

And in this tip, it is using your parameters to dynamically filter data in Power Query.

By doing so, your data that loads will then be dynamically driven by what you specify in your parameter.


Here is an example below.

I created a parameter called Year Version (Which is the last 2 digits of the year).

I gave it the type of Decimal Number.

I then gave it a number of 18. As shown below.

Next I went into my table and went to my column called Data Year that I wanted to dynamically filter my Data Year with my parameter.

I think clicked on the column and selected Number Filters, then Greater Than Or Equal To

This then brings up the Filter Rows Window

Now the magic of the tip is in this next section below. If you look at below, it appears to me that your ONLY option is to put in a value.

BUT if you click on the drop down you now get 3 options!

Now I clicked on the option and changed it to Parameter. I then selected my Year Version parameter from above.

And now my data only show data after and including Data Year 18. And if I change my parameter, it will then dynamically
update my filtered data.

BI-NSIGHT – Power BI (Featured Data Stories, Dashboard Makeover, Office 365 Adoption Content Pack, JIRA Content Pack) – Excel (Get & Transform Update)

Here are this week’s BI-NIGHTS updates.

Power BI – Featured Data Stories

There are some great data stories featured in the Power BI Data Stories showcase. I have to have a special mention to myself that I managed to get my Create Dynamic Periods for Fiscal or Calendar Dates and Dynamic Month Names for Charts also featured which I am really happy about.

You can find all the details here: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Dashboard Makeover

Due to one of the most popular webinars, the Microsoft Power BI Team are going to have another live webinar for the Dashboard Makeover.

It is scheduled for the 13 October 2016.

You can find the details here: Return of the Dashboard Makeover Webinar!

Power BI – Office 365 Adoption Content Pack

In this month’s new content pack from the Microsoft Power BI Team, they have created an Office 365 Adoption Content Pack. This does not only provide great insights into how your organization is adopting and using Office 365, but it also provides great insights into Yammer, Skype for Business, OneDrive for Business and SharePoint usage.

You can find all the details here: Announcing the preview of the Office 365 adoption content pack in Power BI

Power BI – JIRA Content Pack

Here is another content pack released this month, and this month it is JIRA. They provide bug, issue tracking, as well as project management capabilities.

So if you are an existing JIRA customer this can provide some great insights into your data.

Find the blog post here: Explore your JIRA Data with Power BI

Excel – Get & Transform Update

It is great to see that they are still updating the Get & Transform (Power Query) updates in Excel.

With this update they are the enhanced Web Connector with the ability to be able to control the command timeout.

And then also enhanced the CSV/Text connector for the support for fixed-width delimited files.

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

Power BI Service – How to disable Analyze in Excel (Disable Users from Downloading any data from Power BI)

I was recently looking how to help out in the Power BI Community where there was a question around how to disable users from having the ability to disable Analyze in Excel in the Power BI Service.

This could be a very valid situation where you do not want users the ability to export or download any data from the Power BI Service, but rather only have a view of the data, this is how you can achieve this. This includes the Export Data Option on the visuals.

NOTE: When you make this change it does the change for your entire Tenant. So please be aware of this.

A work around might to be enable Row Level Security, so that only the users who have access to the data can see the data. The result would then be when a user uses the Analyze in Excel and they browse the data, they will get data returned based on their security.

Change Tenant Settings

The first thing is that you will need to have access to the Admin Portal in the Power BI Service.

To see if you are an Admin you should see the option below.

Click on Settings to see the list of settings options.

If you can see the Admin Portal, click on the Admin Portal.

Now once in the Admin Portal you will need to click on the Tenant settings as shown below.

Next scroll down and change the following options:

  • Export data to Off
  • Allow users to use Analyze in Excel with on-premises datasets to Off

This is shown below.

NOTE: You have to have to both settings above set to Off for the Analyze in Excel option to be removed.

Then click Apply at the bottom of the page.

After you click Apply you will get a notification letting you know that it will take typically 5-10 minutes for the changes to take effect.

Once the 5-10 minutes is up you can then go back into your reports or datasets and you will see that the option for Analyze in Excel is now longer there.


So by changing the Tenant settings as explained above, your users will no longer have the ability to export any data from the Power BI Service, either by using the Export Data option on a visual, or the Analyze in Excel option on the Reports or Datasets.

BI-NSIGHT – Power BI (What’s coming to Power BI, September Desktop Update, Mobile App Update, Community Blog Highlights, How to Optimize your mobile phone experience, Installing Power BI Desktop through SCCM, Changes to Support, Help Improve Power BI Survey

With Microsoft Ignite completed I have no doubt that we very soon will see some of the announcements starting to be seen in the Microsoft products.

So here are this week’s updates.

Power BI – What’s coming to Power BI

As you can see from the image above there is a lot of things coming to Power BI in Fall 2016 (Here in Australia our Spring 2016) which means it is just around the corner.

There is quite a lot of information on the image above, so for now I think that I will leave it to you the reader to go through and see what is relevant to you.

Power BI – September Desktop Update

In this month’s Power BI Desktop release I am amazed once again at how many features they have put into the product, as well as made things easier to complete.

As in the past I am going to highlight the sections that I feel are relevant.

Report View

I am really amazed to see how quickly they have not only integrated ArcGIS Maps, but with this they have extended the mapping capability within Power BI to be a real game changer. In the past maps have been sometimes used as a wow factor, but with this update it goes from being a wow factor in certain scenario’s to being a great way to gain insights into your data.

Next is the Mobile Report layout, and you could do something similar in the Power BI Service, but being able to do this within the desktop makes the development experience that much easier and quicker to complete. As well as make sure it will look as you would expect it to on the mobile device.


As I alluded to in the previous month’s release that there would be more features being rolled out into the Analytics Pane. And in this month’s release the forecasting feature is something that I am already starting to use. There are some great options as part of the forecast, which will enable you to ensure that you can try and forecast as accurately as possible.

It is also good to see that the Microsoft Power BI team is still investing a lot of time and effort with R. And now that you can use your own R IDE is another great feature, which I am sure will be very welcome to R developers.

Data Connectors

There is a whole host of updates to the data connectors as well as some new data connectors (ProjectPlace & Snowflake Direct Query Support)

Query Editing

Within the Query Editor, they are continuing to not only make it easier to query and transform the data, but now also the ability to view query dependencies.

I also really like the Show Errors experience, as in the past it would add a step into your query transformation and then you would have to go back and delete the step.

As well as other updates and new features, in particular I do like the New Transformation to convert a table to a list. As this list can then be used with the Parameters, so now the list can be dynamically created.

You can read up all about the updates here: Power BI Desktop September Feature Summary

Power BI – Mobile App update for September

It appears that not only has the Microsoft Power BI Team been very busy, but also the Microsoft Power BI Mobile App team also. And in this release they have got some great new features.

The ability to optimize your phone experience with customizable reports, when used in conjunction with Power BI desktop, will make the mobile experience that much better for the people who rely on their mobile platform to view their information on the go.

The addition of the breadcrumb at the top of the mobile app, also is a great feature that will make it that much easier to navigate between reports and dashboards.

It is great to see that Intune MAM is now in the iOS app, as many organizations want to ensure that their data is secure, especially on mobile devices.

Other great updates are the QR for report pages in Android, the ability to manually refresh a tile, and improvements with the data driven alerts.

You can find all the details here: Power BI Mobile Apps feature summary – September 2016

Power BI – How to Optimize your mobile phone experience

This is another great post by the Power BI Mobile app team, where they explain how to optimize your phone experience. They also go into explain how to create the experience using Power BI Desktop, and then also when this version of the report will actually work.

They also go into details to show the enhancements with regards with new capabilities to sort the data in the visuals as well as they have made it easier to drill down into the data. And also in the mobile report view it gives you the ability to still be able to cross-filter.

You can find all the details here: Now in preview: Optimize your phone experience with customizable reports

Power BI – Community Blog Highlights

This is a great area to see the content that other people in the Power BI community have contributed on the Power BI blog. And highlighting what has been released and I have no doubt that it will also help people in their situations.

You can find the details here: Power BI Community blog highlights

Power BI – Installing Power BI Desktop through SCCM

This is a quick mention that if you are using SCCM and you want to keep your users up to date with the latest version there is a blog post from Samuel Lester from Microsoft in how to achieve this.

You can find all the details here: Installing Power BI Desktop through SCCM

Power BI – Support Changes

In terms of how Power BI is supported there are some changes that are happening with regards to new cases being opened. If you are a free user, you will be directed to the Power BI community pages in which the community will be able to assist and help out with issues.

Whilst if you have a paid version of Power BI you can open a case as you currently do.

You can read more here: Growing Power BI Community and changes to how we support our users

Power BI – Help Improve Power BI Survey

If you are looking to help improve Power BI, I would suggest taking the survey, which I have done today and done in the past.

You can find the details here: Help Improve Power BI Desktop

Power BI – Dynamic Row Level Security – Things you need to know to get it working!

Below I am going to explain some of the caveats that you need to be aware of when implementing Dynamic Row Level Security (RLS) in Power BI. Without this I
could not get Dynamic RLS security working for me and my data.

I found that there are a few things that are currently not mentioned anywhere and it took me some time to gain an understanding. So I am hoping that with this blog post it will make it easier for you to implement Row Level Security using Dynamic Security.

Below is the link to a blog post by Kasper De Jonge in which he explains how to very quickly get up and running with RLS, as well as providing a sample Power BI Desktop Model.

Power BI Desktop Dynamic security cheat sheet

And this is what the Relationship Model looks like, which will make the explanation below a little easier to understand.

Things you need to Know!

Below are the things that I learnt and that will help with understanding the bits that make it all work together.

Testing RLS in Power BI Desktop

The first thing that you will need to update if you have downloaded Kasper De Jonge’s Power BI Desktop model is for in the relationships area. This is because it is missing a key tick box, which without it the Dynamic RLS will not work.

  • Go into the (Relationships) area in the Power BI Desktop model.
  • Then edit the relationship between the UserGroup and Group Table, when opening you will see the following below.
  • Now in order for this to work, you will need to apply the tick next to “Apply security filter in both directions”
    • NOTE: If this is not enabled or ticked the Dynamic RLS will not work correctly.
  • So once done it will now look like the following below:

The next thing to note is if you want to test RLS in Power BI Desktop you have to ensure that you have included yourself in both the Users and UserGroup

If not, you will get the following screen when click on the button.

As you can see above the Bar Visual is Blank and the Sales Amount is Blank. This is because with my current login context I am not specified in any of the Dynamic RLS tables.

Which lead me onto the next piece in understanding how the Power BI Service works.

Testing RLS in the Power BI Service

What happened was when I was initially testing this, I put in myself as a user and then a fellow
worker as a user in both the Users and UsersGroup table.

I then uploaded the Power BI Desktop file to the Service. Once it was uploaded I went into the Security for my dataset and put in name under the Roles.

Now what I expected to happen is that when I went into the report I should only see the data for Group B and Group C, the reason is because in the UserGroup table I had rows for Group B and Group C

But when I went and viewed the Report I saw the following below. As you can see I can see all the data and NOT Group B and Group C.

This took me quite a to understand and I did try a whole host of things to get it working.

This is unconfirmed by Microsoft but my own conclusion was that because I am the person who is uploading the Power BI Desktop model into the Power BI Service, I must by default have Admin (Server Administrator) rights to the model.

So no matter what I do, I will always see everything. Which makes perfect sense because I am the author of the model.

So to test this I then shared my Dashboard with another user who only had access to Group C, and when he viewed the dashboard as well as the reports he saw the following below. (NOTE: I did add his email address under Security in the Dataset)


Whilst it is great to now have Dynamic RLS in the Power BI Service I did struggle for some time to get it working, as well as to understand how it all pieces and works together.

And since I now know the above information I have been able to successfully roll out and test other Power BI Models successfully.