BI-NSIGHT – Power BI (Data Insights Summit 2017, Integrate Reports into SharePoint, Dual KPI, Featured Data Stories, Apply Filters to your report per User, Grid Custom Visual)

There have been some really great updates coming out especially from the Power BI team in the past week. So let’s get into it.

Power BI – Data Insights Summit 2017

I can remember that the Data Insights Summit of 2016 was a great success and heard some great content and really deep dive information was shared at the summit. I would really love to attend, so I need to see if I can make it work this year.

For those of you interested here is the link to the website: https://www.microsoft.com/en-us/datainsightssummit

Power BI – Integrate Power BI Reports into SharePoint

I do know that this is one of the features that have been requested by a lot of people. Not only does this mean that the people in your organization do not have to log into the Power BI service, but that it is a seamless experience that is all contained within SharePoint.

I actually did try this myself and as they said, it was as simple as a copy the URL, add the Power BI Web part and you are good to go. Really amazing!

The only small catch is that you will have to have a Power BI Pro user license to use this functionality. And I feel that most organizations or companies that are using SharePoint online this should be really be an issue. As larger organizations or businesses will require the Pro features. And for the low price the amount of Pro features is ever increasing.

You can find the blog post here: Integrate Power BI reports in SharePoint Online

Power BI – Dual KPI

I did blog about this last week with regards to the new Custom Visual for the Dual KPI. And now there is a great video by Guy In a Cube which explains how it works, and how best to use it.

You can find the blog post here: Visual Awesomeness Unlocked: Dual KPI custom visual

Power BI – Featured Data Stories

Congratulations for the winners in the Featured Data Stories in Power BI, there were some great winners and content that the community posted.

You can find the details and additional data stories here: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Apply Filters to your report per User

Here is a great blog post from Kasper De Jonge, where he explains how to filter a report based on the user who has logged in. This does use the Row Level Security but it does it in a different way, to only filter the data.

I am sure that in some instances this would be really useful.

You can find the blog post here: Automatically apply filters to your report per user

Power BI – Grid Custom Visual

Here is another really great custom visual. Where it allows you to have a grid with pagination. So that you do not have to try and fit all your data in a grid onto one page.

Here is the link to the Custom Visual: Grid Custom Visual

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

BI-NSIGHT – Power BI (February Desktop Update, iOS Mobile App with Q&A, Dual KPI Custom Visual, PowerApps Common Data Service) – Excel (Get & Transform Updates for January)

I had to re-write this, as I had a suspicion that the Power BI Desktop would be released soon and here it is, along with other updates.

Power BI – February Desktop Update


This Power BI Desktop update is here, and I have to say that there are some awesome additions and new improvements. So I will highlight below what I think are most relevant.

Report View

It is great to finally have the word wrap not only on the headers but also on the actual rows in the data.

Also having the capability to change the font size for the X and Y Axis in your charts is really handy. As sometimes the font was just too large. Along with this is the chart line thickness, which also can be a great guide for the users consuming the report to highlight something specific and draw their attention to that.

Analytics

I am sure that this will continue to expand and great to see that you now have got the options to calculate the Percent of Row Total and Percent of Column Total.

Data Connectivity

There are some great additions in here and having the capability to select related tables from ODBC and OLE DB connectors.

As well as having the capability to having unified text & CSV connectors, as well as being able to connect to the PowerApps Common Data Service.

Query Editor

I do really enjoy working in the query editor, and now having the option from the column header to select the locale directly from the column header.

As well as now the ability to insert steps into existing queries very easily. Which I have found in the past was not too difficult, but this new addition makes it a lot easier and seamless.

Other

And finally they have included the options to find Solution Templates as well as Partner Showcases very easily. This can assist you if you are looking for a particular solution template, or looking for a partner to potentially assist you with your Power BI project.

You can read all the updated details here: Power BI Desktop February Feature Summary

Power BI – iOS Mobile App now with Q&A

It is fantastic to see how the mobile app has evolved and I think that having the Q&A in the mobile app is really a smart move. Very often you want to find some piece of information that is not currently in a visual. And by using the Q&A you can find the information you are after.

And I have no doubt that you can also actually ask a question and it will translate that into words and bring up the visual you are after.

Here are the blog details: Now in preview: Conversational BI with Q&A on Power BI mobile apps (iOS)

Power BI – Custom Visual – Dual KPI

This is another really interesting visual from Microsoft, the dual KPI. This gives you the ability to have two KPI’s where you can compare different measures over the same timeline. I am sure that this can bring some valuable insights into your data.

You can download the visual here: Dual KPI

Power BI – PowerApps Common Data Service

As you can see above you can now connect to the PowerApps common data service. With the preview release they have enabled 10 perspectives to connect Power BI to. I am sure that as time goes on this list of perspectives will increase.

If you are using PowerApps and want to see how to connect and enrich your data using Power BI, read the blog post below.

Create Power BI reports and dashboards with PowerApps Common Data Service

Excel – Get & Transform updates for January 2017

For those people who use Excel it is great to see that they have updated the Get & Transform with the following.

  • New OLE DB connector.
  • Enhanced “Combine Binaries” experience when importing from any folder.
  • Maximize/Restore buttons in the Navigator and Query Dependencies dialogs.
  • Support for percentage data type.
  • Improved “Function Authoring” experience.
  • Improved performance for OData connector.

As you can see from the list above, there are some great additions as they keep on building on the existing features and I personally have used the Combined Binaries and it works really well and makes things a lot easier when working with Excel and CSV files.

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

020717_0438_PowerBIWhy5.png

Power BI – Why creating columns in the Query Editor is better, quicker and more efficient than creating Calculated Columns.

I have been reading and helping out where I can on the Power BI Community forums. And what I often see is people trying to use the Calculated Columns in the Power BI Desktop Model.

So in this blog post below I am going to explain why it is better, quicker and more efficient to use the Query Editor to create additional columns.

Example Scenario

For this example, what I am going to be doing is searching within the Description
column for a piece of text, and then creating a new column which specifies the area.

The two pieces of text that I want to find are “Gold Coast” and “Brisbane“, which will then go into my new column called Area.

If I cannot find “Gold Coast” or “Brisbane” I will then make it “Unknown

The harder way doing it with a Calculated Column

Below are the steps where you can do this using a calculated column in your Power BI Model.

  • To do this I opened up my Power BI Desktop file and then in the columns clicked on New Column
  • Now in order to find the piece of text that I am looking for I have to use the SEARCH
    function in DAX, but as you can see below in order for this to work I have to add in some additional DAX functions

    Area (Model) =

     IF (

        IFERROR ( SEARCH ( “*Gold Coast*”, [Description] ), -1 ) = 1,

        “Gold Coast”,

    IF (

    IFERROR ( SEARCH ( “*Brisbane*”, [Description] ), -1 ) = 1,

    “Brisbane”,

    “Unknown”

    )

    )

  • As you can see above I have had to use the IFERROR, so that if there is indeed an error I can capture this, and not cause the calculated column to fail.
  • As well as the outer IF condition is where I am saying if the SEARCH = 1 meaning that it is TRUE or Correct, then what Text I want it to display.
  • And as with my two conditions this is for the “Gold Coast” and “Brisbane”
  • And then finally if either of the conditions are not met, then make the output “Unknown
  • NOTE: There might be a more efficient way to do this, and if there is please leave it in the comments section.

As you can see with the above method, this is very clunky as well as very prone to errors.

As well as in the past when I had quite a few conditions I was searching for, it becomes very long and complex.

The easier way, using the Query Editor

Below I am going to show how to achieve the same result in a much easier way using the Query Editor.

  • To do this I opened up my Power BI Desktop file and clicked on Edit Queries
  • Now once the Query Editor Opened I clicked on Add Column in the Ribbon.
  • Next I selected Conditional Column and create it as shown below.
  • As you can see above this is searching for my requirements within the Description column.
  • I then clicked Ok, and then clicked Close And Apply to Load the data into my Power BI Desktop Model.

As you can see to create the conditional column is very easy and simple to search for text and create the additional column.

As well as if there is a requirement for additional conditions, it is very easy to add.

You can see below that both columns have the same output.

Conclusion

So in conclusion you can see that there is a simple way, as well as a more complex way to get the same desired output.

I would also like to note that with my current understanding of Power BI and how the compression and performance works, that it is best practice to ideally create your columns in your source data. If you cannot it is then next best to do it within the Query Editor. And then last resort is in the Power BI Model.

And with each process above being less optimal.

And in my opinion I do not see why you would need to create a Calculated Column, when this can very easily be achieved in the Query Editor.

You can download the sample file here: Calculated Column.pbix

BI-NSIGHT – Power BI (SSRS mid 2017, Mobile Apps Update, OkViz Custom Visuals Updates) – SQL Server vNext (Get Data Experience,

Whilst last week there was not a lot of news in BI, Microsoft has been releasing some information which does make it feel like Christmas in the area of BI!

Power BI – SSRS Mid 2017

This was really some welcome news especially for the people who use SQL Server Reporting Services (SSRS) and are looking to use the Power BI Capabilities for their On-Premise data.

From the blog post, it does appear that there will be a Technical Preview from what I can understand you will be able to download targeted for January 2017.

Along with that they are looking to start with adding features for Custom Visuals, additional data connectors and Power BI Mobile apps viewing.

And finally targeting the Production ready version in mid-2017, which is fantastic.

You can read the details here: Power BI reports in SQL Server Reporting Services: Feedback on the Technical Preview

Power BI – Mobile Apps Update

Just in time for the holiday period some nice updates to the Power BI Mobile app.

As with the image above you can now annotate and share what you want to explain via the iOS app a lot easier. As well as using a QR code to get access to dashboards.

Also they have added GeoFiltering for Android, Custom URL on an Image Tile for iOS and Android and they have also adjusted the offline background refresh so that it will consume less data.

You can find the details here: Power BI mobile apps feature summary – December 2016

OkViz – Custom Visual Updates

The guys from OkViz have released to new Custom Visuals which are the Candlestick and the Color Helper. I do think that often we forget about Color Blind people and this visual is a great way to assist them.

OkViz has also updated all their other existing Custom Visuals, which in the past were missing some key features such as tooltips in some of them.

You can find the custom visuals here: okviz.com

SQL Server vNext – Get Data Experience

This announcement really excites me, having worked in Power Pivot and then Power BI since its inception and now to see this coming into the next version of SQL Server is really amazing.

To me it is a natural progression, and having worked exclusively in Power BI for a few months now I have learnt a great deal, but also have found that things that were traditionally very hard to do in the past are now extremely easy or easy work arounds to shape and get your data in a format that makes for great analysis.

And to see this coming to SQL Server Analysis Services means that I can now leverage all my knowledge when this comes available. Which means that large organizations who have not been able to take advantage of this now can. As well as to have all the data hosted on premise for those that require it.

As I am sure you can understand it will start out with some limited functionality, but this is still a SQL Server vNext, and with each iteration more features will be added.

You can find all the details here: Introducing a Modern Get Data Experience for SQL Server vNext on Windows CTP 1.1 for Analysis Services

SQL Server vNext – CTP 1.1 now available

It is also great to see that there is another version for SQL Server vNext just before Christmas. And there are a whole host of updates.

Firstly details around what is coming in Analysis Services Tabular models, which I have highlighted above with the Get Data Experience, as well as drill-down to established data, which is often what people request. As well as ragged hierarchies and finally enhanced security for tabular models where they are looking to give you the ability to set permissions on individual tables and more granular security!

As well as enhancements for SQL Server on Windows and Linux, which makes it easier to use SQL Server, as well as no doubt perform better.

The blog post details are here: SQL Server next version Community Technology Preview 1.1 now available

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