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.


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

Power Query – Adding Parameters within a piece of text

Below is where I had a situation where I wanted to put in a parameter within a piece of text. This was so that I could then dynamically change the Month Version for my budget and when I refreshed my data it would then use my specific Month Version. This was due to the requirement being that they wanted the ability to select ANY Month Budget Version.


  • I had a requirement where I wanted to use a parameter value, but it would form part of a complete part of text. And as shown below in this example it would be for Mar (March)
  • I wanted the Output to look like the following:
    • Budget_Mar_YR1
  • And the part which is part of the variable is highlighted in BLUE above “Mar
  • I also wanted to re-use this for multiple conditions later in my script.
    • I required it for the following:
      • CY – This is for the Current Year
      • YR1 – This is for the following Year 1, so if I am in 2016 it would be for Year 2017
      • YR2 – This is for the following Year 2, so if I am in 2016 it would be for Year 2018
      • YR3 – This is for the following Year 3, so if I am in 2016 it would be for Year 2019


In order to do this, I went into the Advanced Editor in the Query Editor.

The name of my Parameter was calledBudget Version“, so when using it in the Advanced Editor it would be used with the following syntax below.

#”Budget Version”

In the section below is where I now defined by additional conditions, so that they would be dynamic. An explanation will follow afterwards.

BudgetVersionCY = “Budget_”&#” Budget Version”&“_CY”,

BudgetVersionYR1 = “Budget_”&#” Budget Version”&”_YR1″,

BudgetVersionYR2= ” Budget _”&#” Budget Version”&”_ YR2″,

BudgetVersionYR3= ” Budget _”&#” Budget Version”&”_ YR3″,

As you can see above each line was compromised of the following:

  • I defined our name highlighted in RED
    • BudgetVersionCY
  • Then I started with what our name was, which is highlighted in BLUE
    • “Budget_”
  • Next is where I inserted our Parameter highlighted in PURPLE
    • &#” Budget Version”&
    • NOTE: When you want to add additional TEXT or parameters you have to open it with the ampersand “&” as well as close it off (or end it) with an ampersand “&” also.
  • And then finally I added some more text at the end highlighted in BLUE again.
    • “_CY”

Then later in my query is where I put in my conditional statements into my Conditional Column as shown below.

#”Filtered Rows” = Table.SelectRows(#”Added Custom2″, each ([Budget Version] = #”
” or [Budget Version] = #” BudgetVersionYR1” or [Budget Version] = #” BudgetVersionYR2” or [Budget Version] = #” BudgetVersionY3“)),

Final Note

Just one thing to note, is when I put in the following syntax into my Query Editor you will lose the capability to edit it by using the settings or Gear icon

BI-NSIGHT – Power BI (Custom Refresh Schedules for Live/DirectQuery, Report Theme Gallery, Custom Visuals Community Site, Filter a report with a URL query string, Summit EMEA)

Once again the world of BI is very busy with great new features and other details which I have put into this blog post below.

Power BI – Custom Refresh Schedules for Live/DirectQuery

I have to say I have noticed that the Power BI team are currently releasing a lot of features which give a lot more granular control to different things in the Power BI Service. From my point of view this is fantastic as it allows more flexibility in the product, which means it can potentially be released to a wider audience in some instances.

This latest release with regards to the Custom Refresh Schedules for Live/DirectQuery is another great granular feature. This allows the user to now control how often to refresh the cash, which is used for the Dashboards tiles. And can help ensure that it can be optimized.

You can find the blog details here: Announcing Custom Cache Refresh Schedules in the Power BI Service

Power BI – Report Theme Gallery

Due to the release of the preview feature of Report themes into Power BI Desktop, the Power BI Team has now release a Report Theme Gallery.

This is where you can showcase your theme that you created, as well as share it with others who might want to use it.

I personally think this is great, and once again shows how Power BI is a community where we can share our knowledge.

Here are the details: Power BI Community Report Theme Gallery

Power BI – Custom Visuals Community Site

Here is yet another great initiative from the Power BI team, and this is all around the Custom Visuals where they have created a Custom Visuals Community site.

This will be a great resource for people who are looking to develop Custom Visuals as well as assist other people with issues.

You can find all the details here: New custom visuals community site: developers and users unite!

Power BI – Filter a report with a URL Query String

Apparently this has been in the documentation for some time, but was only publically shown now by Adam Saxton (Guy In a Cube).

In the blog post they show how you are able to filter reports in the URL using a Query String.

This functionality used to exist in Power View for SharePoint and I can remember using it in various scenario’s where you wanted the report to automatically filter. This is great to see that you can now do this in Power BI also.

You can see Adam’s video and blog post details here: Filter a report with a URL query string parameter

Power BI – Summit EMEA

If you are in the EMEA region I would suggest if possible attending the Summit EMEA, where you can learn about Power BI, as well as discover other details around Power BI and the various insights it provides.

You can find the details here: Dive into Power BI at Summit EMEA

Power BI Query Editor – Getting IP Address Details from IP Address

I recently had a question from a user in the Power BI Community page who wanted to know where the people were coming from based on their IP Address. The IP addresses were stored as part of the dataset, but to try and go and do this with the IP Address database meant that you would then need to go and translate the IP addresses into a number, to cross reference across the IP Address ranges.

My solution below rather uses the web lookup, which will work using any dataset, as well as simple and easy to use.

Adding the Function into your Query Editor

  • The first thing that you will need to do is to create the function which I did with the following steps below.
    • Click on New Source, and then select Blank Query
    • Next rename it from Query1 to fn_GetIPAddressDetails
      • You can do this by right clicking and select Rename
    • Next in the Home Ribbon under the Query section click on Advanced Editor
    • Now paste in the following Power Query (M) syntax
      Source = (#"IP Address" as text) => let
      Source = Json.Document(Web.Contents("" & #"IP Address")),
      #"Converted to Table" = Record.ToTable(Source),
      #"Transposed Table" = Table.Transpose(#"Converted to Table"),
      #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
      #"Promoted Headers"
    • Then click Ok.
    • Now you should see the following for your function
  • What the above does is it takes the input of IP Address
  • Then what it does it takes the IP Address and then using the above service looks up the IP Address and returns the details.
  • Next it converts the JSON to a table.
  • After which I then transposed the data.
  • And finally promoted the first Row as Headers

Using the Function with your Data to output the IP Address details

In this step I will now show you how to use this function to get the output from your IP Addresses, in your data.

  • 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 fn_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 left them all selected and clicked Ok.
  • And as you can see below, here is the first 3 columns from the list
  • I then loaded my data into my Power BI Model and created a map visual using ESRI

So in conclusion you can see it is very easy to use Power BI to create functions, which can iterate over a your dataset and give you a meaningful output with not a lot of effort, where in the past this used to take a significant amount of effort.

You can download the sample file here: Get IP Address Details.pbix

Power BI – Power Query/Query Editor Automated Source Control

I saw a blog post recently where Jay Killeen was talking about how he handles his Source Control for the Query Editor or Power Query. And this got me thinking of how I could automate this in a quick and easy manner. So below is how I achieved this.

My solution below, uses a BAT file and 7zip, which means that the process can be automated using a Scheduled Task, and be continuously running in the background.

All that you will need is 7Zip and command prompt (CMD).


For this example, I will show how I get the DataMashup file from your Power BI Desktop file, whilst your Power BI Desktop file is still open.

Then take this file and copy it to OneDrive (You can potentially use this with any other systems such as Google Drive, Drobox etc.) with a Date Timestamp which will make the file unique, as well as know when it was created.

And finally, I will also show how you can revert to a previous version of the file if required.

NOTE: You can also use this process if you want to Share your Power Query/Query Editor files with other people. The people who require the changes must follow the steps in the Reverting back to a Previous Version of the DataMashup File


Below you will need to download and install 7Zip (32bit) for this to work.

7Zip (32bit)

What is the Power Query/Query Editor File and where do you find it

I thought it would be good to quickly explain how I came to know about the Power Query/Query Editor file.

  • If you change the file extension of your Power BI Model from .pbix to .zip, this then enables you to view the contents that make up the .pbix file.
  • So as you can see below when I have renamed my .pbix file I see the following:
  • Now after I had been doing some investigations I found out that the file called DataMashup is the file that contains everything in the Power Query/Query Editor.

So this is the file that we need to use for our Source Control for the Power Query/Query Editor.

BAT File explanation

Below is the BAT file that I have created in which it is doing all the steps which are explained below.

NOTE: You will be required to change the following variables in the BAT File, so that it will work for your data.

  • Change this to the location of your Source Directory of your Power BI File

    set SD=”C:\Users\guava\Downloads\”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.
  • Change this to the location of your Destination Directory where you want your DataMashup file to be copied to

    set DD=”C:\Users\guava\OneDrive\BI\Power BI\Power Query Versions\Data Gateway Test”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.
  • Change this to the Filename of your Power BI Desktop File. NOTE: Do not include the extension.

    set PBIX=”Data Gateway Refresh Test”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.

Here below is the code from the BAT file which explains what each step is doing.

The final step is to save the BAT file to an appropriate File Name.

With my example, I gave it the file name of: PBISC – Data Gateway Test.BAT

How it works

Now once you have you updated the BAT file with the correct variables it is as simple as running it from the command line.

NOTE: You have to first save your Power BI Desktop file in order for the changes to be saved into the DataMashup file.

NOTE II: I would recommend that you put in the full File Path for your BAT file to ensure that it will always run

  • I created a specific Folder Structure in my OneDrive Folders, so that I have a separate Folder per PBIX file.
    • As you can see it is currently empty.
  • Next I run my BAT file from the command prompt
  • Once it has run I see the following in command prompt:
    • As you can see above it is the output from the BAT File.
    • NOTE: You can also run this BAT File from the Run command, which will also run successfully.
  • And I now see the file in my OneDrive Folder.

Example of Saving Changes

So in the steps below I will now show after I have made a change in the Power Query/Query Editor how the change in both the file name and size is automatically copied and synced to my OneDrive Folder.

  • I have got my Query Editor open in Power BI Desktop
  • What I will now do is to duplicate the table called “Budget Data without New Year“, as well as disable the Loading of this dataset (This is because I want to show how the DataMashup file changes.).
    • As you can see above I have duplicated the table and Disabled the “Enable Load
  • I then click on Close and Apply, and then save my Power BI Desktop File.
  • Next I run my BAT file again
  • Now you can see in the picture below I have got my new file.
    • You can also see that the file size is slightly larger due to having duplicated
      my table in the Query Editor (which includes all the steps for the table)

Reverting back to a Previous Version of the DataMashup File

The whole reason for the automating the version control is so that in the event you need to go back to a previous version, we have the files required to do this. And I explain how to do this in the steps below.

  • The first thing that you will need to do is close your Power BI Desktop file.
  • Next go to the location of where you have stored the copies of the DataMashup files.
  • With my example, it is in the following location:
  • Now I took a copy of the file that I want to revert back to.
  • With my example I am going to revert back to the first file called DataMashup_02-03-2017_20_08_18 because this is the file that does not have the duplicated table.
  • I made a copy to my Documents Folder.
  • I then rename the file from DataMashup_02-03-2017_20_08_18 to DataMashup
    • NOTE: This is because in the Power BI file format it will look for a file called DataMashup
  • Next I go back to my Power BI Desktop file and rename it from Data Gateway Refresh Test.pbix to Data Gateway Refresh
    • It will prompt you asking are you sure you want to change it?
    • Click Yes.
  • Now I went into the zip file by double clicking on the file Data Gateway Refresh
  • Next I went back to my Documents folder and copied the DataMashup file.
  • I then went back to my zip file and then right clicked and selected Paste
    • This then prompted me with the following window as shown below.
    • I clicked on Copy and Replace
    • NOTE: This is so that it will replace the DataMashup file with my previous version.
  • Now I had to refresh the zip file to see the changes.
    • Which you can see below the file size has gone from 34k to 27k
  • Now I renamed my zip file back to the pbix file.
    • It will prompt you asking are you sure you want to change it?
    • Click Yes
  • I then opened my Data Gateway Refresh Test.pbix and went into the Query Editor
    • As you can see with the picture below I do not have my additional table
    • It is back to 7 queries.
  • I have successfully reverted to a previous version.


So in conclusion you can see how I automated the process of not only copying the DataMashup file, but also using a system like OneDrive I can automatically sync this data to the cloud and use the built in versioning control.

Here is the link below to the BAT file that I created, for anyone who wants to use it for their own automation and source control for the Power Query/Query Editor data.

I also asked a good friend of mine to create a PowerShell script doing the same thing. So if you want to use PowerShell instead of the BAT File please find the files below.

NOTE: For the BAT file the extension is txt-BAT and for the PowerShell script the extension is txt-ps1, this is so that it should be able to be successfully downloaded or emailed.

PBISC – Data Gateway Test.txt-BAT

Power BI DataMashup.txt-ps1

And as always if anyone has got any comments or suggestions please let me know. As I have a feeling that there might be some great ideas that come from this.

Power BI – Getting your report to always filter this month’s data dynamically

I had a requirement where I needed to have the capability to set the filter once on a report for the current month and then it dynamically move as each month progresses.

I will show how I completed this in the steps below.

NOTE: As with almost all Power BI Models, this is going to be leveraging off my Date table that I created. If you want to know how to create your own Date table you can follow my blog post Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

Getting the Current Year-Month in the Query Editor

The first thing that I needed to do was to create a new column in the query editor which will show what the current month is.

  • In order to this it involved first create a column which had a combination of the Year and Month Number.
    • NOTE: This was so that I was selecting the current month.
  • I went into the Query Editor and created the following column as shown below for the Year and Month Number
    • I clicked on Add Column in the ribbon then Custom Column
    • Below is the syntax that I put in the Custom column formula

      Number.ToText([Calendar Year]) & “-” & Number.ToText( [Calendar Month Number])

      • What I had to do above is because the [Calendar Year] and [Calendar Month Number] are formatted as Numbers, I had to change this to Text with the Number.ToText highlighted in GREEN above.
    • As you can see above, what I did here was I combined the Calendar Year and Calendar Month Number, so that the output was as sown below.
  • Next is where I created another column which will be the current Year-Month.
    • I clicked on Add Column in the ribbon then Custom Column
    • Below is the syntax that I put in the Custom column formula

      Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))) & “-” & Number.ToText(Date.Month(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)))

      • To explain this best was for me to explain this from the inside out, so starting with the DateTimeZone.FixedLocalNow() is where it is getting your Local Data for the time zone that you are in. This is highlighted in ORANGE.
      • Next I put in the DateTimeZone.SwitchZone() and set this to 10.
        • NOTE: The reason that I did this, is so that when this Power BI Desktop file gets uploaded to the Power BI Service, it will still show the correct time for my current time zone. This was highlighted in LIGHT BLUE
      • Next what I did to get the Year, I used the Date.Year function which will just return the Year, for what is currently being requested, and in this case is the current date. And is highlighted in PURPLE
      • Next what I did to get the Year, I used the Date.Month function which will just return the Month, for what is currently being requested, and in this case is the current date. And is highlighted in GREY
      • And finally I had to do above is because the Date.Year and Date.Month
        automatically get formatted as Numbers, I had to change this to Text with the Number.ToText highlighted in GREEN above.
    • So as you can see below for my current date (15 Feb 2017) I got the following in my column called Current Year Month which should be 2017-2

Creating the Comparison Column and required output

In the next steps below I will show how I now easily created the comparison column with the required output.

  • As with the example I wanted to get the Current Month.
  • I went into the Query Editor and created the following column as shown below for the Year and Month Number
    • I clicked on Add Column in the ribbon then Conditional Column
  • I then put in the following conditions as shown below.
  • As you can see above I used both of the columns [Year-Month] and [Current Year Month] to evaluate my condition.
  • The result of the conditional column is shown below.
  • And what I did to confirm that it was working correctly is I filtered my [Is Current Month] column to “Current Month“, so that I should only see the Dates for Feb 2017, which is what I saw as shown below.
    • NOTE: I did remove this filter before loading the data into my Power BI Model.

Putting the page filters in place for current month

So the final step I did was to now put in the page filter, or even using a slicer to filter the data to always show the current month.

  • So as you can see below what I did was I created a Month Selection slicer and applied the Interaction Only to the chart on the right hand side.
  • Whilst the chart on the left hand side I set the interaction to None

Additional column filters for different periods

Below are some additional column filters, so that you can use them for your own periods.

In order to do this, you will use the code below and use it when creating the column called [Current Year Month] or new column.

Current Fiscal Year

Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))-1) & “-” & Text.End(Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))),2)

  • NOTE: The above has the following output.

Current Date

  • Date.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))
  • NOTE: The above has the following output.


So as you can see from the final output, by creating the columns in the query editor and then using the output as a slicer in the above example or as a page filter, you can then apply this to your data to always be current.

One thing that you need to be made aware of, is that in order for this to work correctly, you will have to refresh your data based on the frequency of your filter. As with the above example it was Monthly, so I would have to ensure that my data was updated monthly.

Finally you can download a copy of the above file here: which has got the additional columns also: Filtering Reports to Current Month.pbix

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.