Power BI – Turning off (Disabling) Native Database Queries

In this quick blog post I am going to show how to turn off or disable having the dialog
box which pops
up when using Native Database Queries.

This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop.

NOTE: I was often prompted with the following below for EACH native database query.

As you can see below this is typically what would happen if I ran a native database query

  • I would first get the yellow car asking for permissions to run the native database query.
  • Then once I clicked Edit Permission I then got the following Window, showing me the query that is going to be run.
  • Once I click Run it would then run the query.
  • So this can be time consuming especially if I had multiple queries.

How to turn off or disable the Native Database Queries

In the steps below I will show how to easily turn (Disable) the native database queries

NOTE: I could complete the steps below from either being in the Query Editor or within Power BI Desktop.

  • Click on File and then Options and settings and then Options
  • Then under GLOBAL I clicked on Security
  • Then in the Security settings at the top it has got a section which says Native Database Queries you will see an option.
    • And there is a tick
      next to “Require user approval for new native database queries
    • I removed the tick.
  • Then click
    Ok.

Now I went back and ran my native database query and it simply
ran with no requests to Edit Permissions or to Run the query.

Conclusion

I have shown how to disable the native database queries which not only helps when I am developing but also assists me when my Power BI Desktop file is refreshing.

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…)

Using Power BI to ensure Daylight Savings Dates and Times change dynamically

I was helping on the Power BI Community and it struck me that there currently was not a way to easily manage Dates and times for Daylight savings. In this blog post below, I will show how I achieved this by using parameters and little bit of M magic (Directly Editing the M Code).

As part of completing this solution I also included the UTC Offset, which I did blog about previously Power BI – Did you know ALL Power BI Services Servers are in UTC? Now how to handle it for Dates & Times which will ensure when the Power BI Desktop file is uploaded and refreshed within the Power BI Service it displays the correct Date and Times.

(more…)

BI-RoundUp – Power BI (June Desktop Update – Data Insights Summit Details – Power BI Embedded Pricing & Details – Power BI Premium Generally Available – Power BI Report Server Generally Available)

I was fairly certain that there were going to be a whole host of updates and additions to Power BI, and I can say that I am not disappointed. There is a whole host of information below, where I will endeavour to try and get it all to you, so this might be a bit long to read, but it will be worth it.

(more…)

BI-RoundUp – Power BI (Service Update April & May – Personal Gateway Update – How to Purchase Power BI Premium – Managing Power BI Premium – Memory Consumption in Power BI File – Power BI – Exposing M Code)

It has been a busy week with the start of another month leading into June, so here are the updates. I am pretty confident that with the Microsoft Data Insights Summit happening Monday & Tuesday of next week that the new version of Power BI Desktop, as well as some new features will be revealed then. And I cannot wait, I always look forward to the start of each and every month.

(more…)

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.

Example

  • 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

Solution

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] = #”
BudgetVersionCY
” 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
      let
      Source = (#"IP Address" as text) => let
      Source = Json.Document(Web.Contents("http://freegeoip.net/json/" & #"IP Address")),
      #"Converted to Table" = Record.ToTable(Source),
      #"Transposed Table" = Table.Transpose(#"Converted to Table"),
      #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
      in
      #"Promoted Headers"
      in
      Source
    • 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 http://freegeoip.net 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).

Example

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

Requirements

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
correctly.

  • 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 Test.zip
    • 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 Test.zip
  • 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.

Conclusion

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.

Conclusion

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