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

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.

Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

I have been working on some new projects and one of the things that I found that was whilst you can find examples of creating Date tables, I could not find any examples of where the Date table included Fiscal Attributes such as Fiscal Month, Fiscal Quarter and Fiscal Year.

So in this blog post I am going to show how you can easily create your own date table with Fiscal Attributes in a few simple steps.

I have also created it in such a way that you can dynamically define your starting year, as well as your starting month for your fiscal attributes.

Below is a sample output of what I will explain below.

Creating Parameters

The first step is to create two parameters. These two parameters will be used to dynamically load our Start Year, as well as the Fiscal Start Month

Please NOTE you must create them with the same name and attributes. This is to ensure that the Power Query script will work correctly in the later steps.

This is how I created the Parameters.

  • You can go into Edit Queries, then click on Manage Parameters and I put in the following for Start Year
    • As you can see above I put in the Name “Start Year”
    • I then changed the Type to Decimal Number
    • And then put in my Current Value of 2014 for the year I want it to start from.
  • Next is the Parameters for Fiscal Start Month
    • As you can see above I put in the Name “Fiscal Start Month”
    • I then changed the Type to Decimal Number
    • And then put in my Current Value of 7
  • Once completed I had the following under Queries

Creating Date Table with Fiscal Attributes

In this next step is where I used the Power Query language “M” to create my Date Table with the Fiscal Attributes.

You are welcome to read through what I have done, but all that you will need to do is the steps below and copy and paste to get it working. Nothing else needs to be modified in the code below, due to the fact that I worked it all out dynamically using the parameters we created above.

For those people who are interested after the code I will highlight what I did to create the Fiscal Attributes.

UPDATE: 10 Feb 2017

I have put some additional conditions in the code depending on when you want your End Date to be.

  • If you want the End Date to be your current Date then you can leave the code as it is.
  • If you want the End Date to be 4 years into the future and the last day of that year, then uncomment the second line. (Remember to comment out the previous line)
  • And finally if you want the End Date to be the End Date Parameter then uncomment the third line. (Remember to comment out the previous line)

Now in the Query Editor complete the following to create the Date Table

  • Click on New Source, then Blank Query
  • Next I gave the Query a name from Query1 to Date
  • Then in the Home Ribbon, click on Advanced Editor under the Query section
  • This will then open the Advanced Editor as shown below.
  • Now copy and paste in the code below.
    let
     Date = let
    Source = List.Dates,
    FiscalMonthCalc = 12-#"Fiscal Start Month",
    
    // 1. Uncomment this line if you want to get your end date being Today's Date of the Refresh
    #"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),
    
    // 2. Uncomment this line below if you want your end date to be 4 years into the future
    //#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(Date.AddDays(Date.EndOfYear(Date.AddYears(DateTime.FixedLocalNow(),+4)),-1)) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),
    
    // 3. Uncomment this line if you want to get your end date being the Parameter called "End Year"
    //#"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(#date(#"End Year", 12, 30) - #date(#"Start Year",1,1)), #duration(1, 0, 1, 0)),
    
    #"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
    #"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
     #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Fiscal Year", each if [Month Number] < #"Fiscal Start Month" then [Year] else [Year]+1 ),
     #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fiscal Month", each if [Month Number] < #"Fiscal Start Month" then [Month Name] else [Month Name] ),
     #"Added Custom7" = Table.AddColumn(#"Added Conditional Column1", "Fiscal Month Sort Order", each Number.Mod(Date.Month([Date])+FiscalMonthCalc ,12)+1),
     #"Added Conditional Column3" = Table.AddColumn(#"Added Custom7", "Fiscal Quarter", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "Q1" 
     else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "Q2" 
     else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "Q3" 
     else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "Q4" 
     else "Q Unknown" ),
    #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Fiscal Quarter Sort Number", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "1" 
     else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "2" 
     else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "3" 
     else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "4" 
     else "Q Unknown" ),
     #"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column4",{{"Fiscal Quarter Sort Number", Int64.Type}, {"Fiscal Month Sort Order", Int64.Type}, {"Fiscal Year", Int64.Type}}),
     #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Date", "Calendar Date"}, {"Month Number", "Calendar Month Number"}, {"Month Name", "Calendar Month Name"}, {"Quarter Number", "Calendar Quarter Number"}, {"Quarter Year", "Calendar Quarter Year"}, {"Short Year", "Calendar Short Year"}, {"Year", "Calendar Year"}})
    
    in
     #"Renamed Columns3",
     #"Duplicated Column" = Table.DuplicateColumn(Date, "Calendar Date", "Calendar Date - Copy"),
     #"Calculated Week of Year" = Table.TransformColumns(#"Duplicated Column",{{"Calendar Date - Copy", Date.WeekOfYear}}),
     #"Renamed Columns" = Table.RenameColumns(#"Calculated Week of Year",{{"Calendar Date - Copy", "Week Number of Year"}}),
     #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Calendar Date", "Calendar Date - Copy"),
     #"Calculated Week of Month" = Table.TransformColumns(#"Duplicated Column1",{{"Calendar Date - Copy", Date.WeekOfMonth}}),
     #"Renamed Columns1" = Table.RenameColumns(#"Calculated Week of Month",{{"Calendar Date - Copy", "Week Number of Month"}})
    in
     #"Renamed Columns1"
  • Then click Done
  • Now you should see your Date Table with the Fiscal Attributes created.

Next I will show you how by changing the Fiscal Start Month how it dynamically changes the Fiscal Month Sort Order and Fiscal Quarter

  • As with our current example it is setup with the Fiscal Start Month of 7.
  • So the month of January will be the 7th Fiscal Month starting from July.
    • As well the Fiscal Quarter will be Q3.
  • Now I will change the Fiscal Start Month to 11
    • Click on the Fiscal Start Month and then change it from 7 to 11
    • As you can see below it currently is set to 7
    • And once done it is now changed to 11
  • Now when you go back to your Date table you will see the following changes.
    • For the Month of January, the Fiscal Month Sort Order will be 3
    • And the Fiscal Quarter will be Q1
  • You can try this for yourself and modify the parameters as required.

Technical overview on how I created the Fiscal Attributes

For the people who want to simply copy and paste and get it working you can skip to the conclusion, but for the people who want to understand how I did this please read below.

I had to come up with a way to get the right Fiscal Month Sort Order where the input could be dynamically driven. I did a fair bit of searching until I found someone who had done it in Excel, after which I modified it so that it would work with Power Query as well as bring back the right Fiscal Month Sort Order.

This is the code below, with the explanation afterwards.

  • The first part is where I created the FiscalMonthCalc

    FiscalMonthCalc = 12-#”Fiscal Start Month”,

    • What I am doing above is taking the total months in a year (12) and subtracting the Fiscal Start Month Parameter
    • This will be used in the code below so that we can get the right output.
  • Next is where I created the Fiscal Month Sort Order with the code below.

    Number.Mod(Date.Month([Date])+FiscalMonthCalc
    ,12)+1

    • As you can see above the first part highlighted in GREEN is where I am using the Mod (Divides two numbers and returns the remainder of the resulting number.)
    • In the next section is where I am getting the Month Number from the current Date row in the Date table highlighted in PURPLE
    • Now in order to get the number for our Mod, I then added this to my FiscalMonthCalc highlighted in RED
      • I had to do this in order to get the number for our Mod to be correct.
    • Then I next put in the divisor for our Mod which was 12 due to their being the 12 months of the year highlighted in ORANGE
    • The final part is where I had to increment the number by 1 to show the correct Fiscal Month Sort Order highlighted in BLUE

Then in order to create the Fiscal Quarter and Fiscal Quarter Sort Number I could then use the Fiscal Month Sort Order column created above.

This is the amazing thing with Power Query is that once I created my column I needed the next steps to create the Fiscal Quarter was as easy as defining our Quarters as we know which is every 3 months as shown below with the sample code.

#”Added Conditional Column3″ = Table.AddColumn(#”Added Custom7″, “Fiscal Quarter”, each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then “Q1”

else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then “Q2”

else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then “Q3”

else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then “Q4”

else “Q Unknown” ),

And because it is all driven off the Fiscal Month Sort Order it will always be correct due to being dynamically created.

Conclusion and download File

So in conclusion here is an easy way to create a Date table with Fiscal Periods that you can modify to your specific requirements.

Here is a link where you can download an example file: Creating Date Table with Fiscal Attributes.pbix

BI-NSIGHT – Power BI (Secure and Audit Power BI, Data Driven Parameters, Snowflake Data Connector) – Excel (Get & Transform Updates / Power Query Updates)

Here are the BI updates for this week.

Power BI – Secure and Audit Power BI

One of the questions that is regularly asked with regards to Power BI is around how secure the data is. And along with that is how do people gain access.

This new update goes a long way not only to make Power BI more secure. But also to have the ability to look at the audit logs and see who is doing what if required.

I have no doubt that this will go a long way in helping organizations secure their information, as well as be compliant from an audit perspective.

You can find the details here: Secure and Audit Power BI in Your Organization

Power BI – Data Driven Parameters

This is another great blog post by Chris Webb where he explains how to use the new updates in Power BI which enable you to now use Data Driven Parameters. So now you can dynamically have your requested parameters come from a list, which can be derived from an existing data set.

You can read up about it here: Data-Driven Power BI Desktop Parameters Using List Queries

Power BI – Snowflake Data Connector

In this blog post from the Microsoft Power BI team they explain what the Snowflake data connector is and how to connect and use it.

I am sure that it will be very welcome for the existing and potential Snowflake customers.

You can read up about it here: Power BI enables connectivity to Snowflake

Excel – Get & Transform Updates / Power Query Updates

IT is good to see that the Excel team has also found a way to keep on adding features and updates to the Get & transform or Power Query within Excel. I often find that they get released first to Power BI Desktop, but then they soon make it into Excel also.

This month there is a whole host of updates as shown below.

As you can see from above there are additional data connectors, as well as changes in the query editor. As well as finally making it easier to extract Date and Time functions from your existing columns. Which in the past can be rather tricky?

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