BI-NSIGHT – Power BI (Meet 1:1 with Microsoft Expert – May & June Webinars, Sample files as PBIX, Quick Measures Gallery, Mobile App Update – April)

Here are this week’s updates, with May starting next week I am looking forward to some interesting updates with regards to Power BI.

Power BI – Meet 1:1 with Microsoft Expert

If you are attending the Microsoft Data Insights Summit you now have the opportunity to have a 1:1 session with a Microsoft Engineer for 15 minutes. If I was attending this is something that I would have already signed up to before blogging.

You can find the details here: Meet 1:1 with a Microsoft expert at Microsoft Data Insights Summit – book your appointment today!

Power BI – May & June Webinars

As you can see below here are the May & June Power BI Webinars

You can find all the details for the Webinars here: May and June Webinars: Power BI Security, Best Practices from the Microsoft Operations Team, Power BI Embedded, Marco Russo on Design and more!

Power BI – Download Samples as PBIX Files

I have to say that in the past when I was using the Power BI Sample data, I did wonder why there were not any sample PBIX files.

Now they have created and enabled the downloading of the PBIX files in the link below.

The Power BI samples as .pbix files

Power BI – Quick Measures Gallery

After the success and implementation of the Quick Measures in Power BI Desktop there now is the Quick Measures Gallery.

In this gallery you can now submit what you would like to see added to the future Quick Measures in Power BI. I think that this is a great initiative, because it not only allows people to have their own measures featured in the Power BI Desktop, but it also allows people to view the Gallery, view the measures and use them for their own requirements.

You can find the blog post here: Introducing the Quick Measures Gallery

Power BI – Mobile App Update April

In this month’s Mobile App update they have released to all the platforms the capability to be able to have the background colour. As well as a lot of bug improvements and performance fixes.

Also in the blog post they did mention the option to develop custom visuals for Mobile phones. Which I thought was quite interesting.

You can view the blog post here: Power BI Mobile apps feature summary – April 2017

Power BI – On-Premise Gateway Configuration steps to an Oracle Database

I was recently working in an environment where the requirement was to connect to an Oracle database using the On-Premise Gateway, so that we could either DirectQuery or import the data and then refresh it using the On-Premise Gateway. So the steps below detail how to complete this successfully.

The example below was connecting to an Oracle databased called TRID

Downloading and installing Oracle Data Access Components (ODAC)

The installation of the ODAC is fairly straight forward and already has been very well documented in the Power BI website.

You can view the link below in order to download and install the correct ODAC version for your Power BI Desktop Installation.

Connect to an Oracle database

NOTE: Part of the installation is that you will need to setup and configure your tnsnames.ora file in order to successfully connect to the Oracle Database.

Configuration of tnsnames.ora

These steps below were configured by the Oracle DBA where I am currently working, I thought I would highlight what needs to be configured below in order to successfully connect to the Oracle Database.

I found this out when trying to connect to the Oracle database and it appears that this is what is required to ensure that I could connect successfully.

Below is a working version of my tnsnames.ora file


The important things that I needed to have configured were the following:

  • The first part is the name which is highlighted in GREEN above with my server called TRID
  • The other important part is to ensure that you have got the SERVICE_NAME
    configured and resolves back to the actual server with the Fully Qualified Domain Name (FQDN) highlighted in PURPLE.
    • As with my above example this was

Setting the connection to the Oracle database in Power BI Desktop

The next step was for me to set up or create the connection to my Oracle Database.

NOTE: In my example below we connected using a database user and password and not integrated Windows Authentication.

  • I clicked on Get Data and then selected Oracle database
  • Then I clicked Connect.
  • NOTE: In my instance I got the following window shown, which explains that I have an older version of ODAC, I clicked Ok to continue
  • On the Oracle database window it now prompted me for the Server and if I am going to use the Import or DirectQuery Data Connectivity mode, as well as additional advanced options.
    • In my example I simply put in the Server Name and left it defaulted to Import
    • I then clicked Ok
  • The first time I connected it prompted me for the Security Connection details.
    • As mentioned before I used the Database security connection details
    • I then clicked Connect.
  • This then brought up the Navigator, which from then I could then connect to my required tables.
  • And then loaded the data into my data model and completed my Power BI Desktop File.
  • Once I had completed my Power BI Desktop file I then uploaded it into the Power BI Service.

Configuration of the Power BI Service Gateway

In the steps below I now will show how I created and configured the Gateway to connect to the Oracle database.

NOTE: I had already installed and configured the On-Premise Gateway within the domain I am working in. You can reference this installation guide: On-premises data gateway

  • I logged into the Power BI Service and then clicked on Manage Gateways
  • I then clicked on Add Data Source
  • Now this is one of the MOST IMPORTANT configuration steps when I was configuring the Data Source Name
    • This Data Source Name MUST be identical to the Server Name I created in my Power BI Desktop File Data Source Connection.
    • As I did previously I used the Oracle Server name TRID
    • So now when I created my new Data Source I configured it with the following below, again NOTING that the Data Source Name is identical to my Power BI Desktop File Server Name as highlighted below.
    • Then I clicked Apply.
    • And as you can see above it says Connection Successful which means that my Gateway successfully connected to the Oracle database.
  • The final step that I did was to give the required users access to the Gateway
    • NOTE: You must add the users in here that you want to be able to connect to the Gateway Data Source and configure the data refresh.

Configuring the dataset to use the Gateway connection

The final step that I had to complete was to configure my dataset to use the Gateway connection.

  • I went into the Power BI Service.
  • Then clicked on Settings and selected Settings
  • Then I clicked on Datasets
  • I then went to my dataset that I had previously uploaded, where I wanted to connect to it using the Gateway connection.
    • I then expanded the Gateway connection
    • Now I had the option to Use a data gateway
    • And in here I selected my Gateway that I had previously configured.
    • I then clicked Apply.
  • Then the final step was to test that it was all working and I did this by going into the Datasets and clicking Refresh now
  • Once that had completed I went back into Settings, then Datasets and clicked on my dataset.
    • Then at the top I clicked on Refresh History
    • And then I could view that my refresh completed successfully.

This will hopefully guide you to get your Oracle connected with the On-Premise Gateway.

Another quick note is that the steps when connecting to a SQL Server are very similar, especially the steps in the Power BI Service.

Any comments or suggestions are welcome and please leave them in the section below.

BI-NSIGHT – Power BI (Community Blog Highlights – Power BI Germany – Latest Updates for Power BI Desktop – How Microsoft uses Power BI to run its Cloud Business – Connecting Datasets to Power BI Service) – SQL Saturday Brisbane (I’m presenting) – SQL Server (SQL Server 2017 – DAX Editor for SSDT – New Get Data Experience for SSDT)

I thought that this might be a quieter week, but once again there are a whole host of updates from Power BI updates, to SQL Server 2017 announcements. Quite a bit to read through so I hope you enjoy it.

Power BI – Community Blog Highlights

I actively participate in the Power BI Community, as well as when I feel like I have something meaningful that will also contribute to the Power BI Community get it published onto the Power BI Community Blog. And in this month’s highlights I am thrilled to have my latest blog post as part of the highlight.

The other posts are below:

Power BI – Infographic for Power BI from an end to end perspective, by Gilbert Quevauvilliers

Design Pattern – Groups and Super Groups!, by Greg Deckler

Get Your Own Power Query Editor using Notepad++, by Lars Schreiber

What I Learnt Building Power BI Custom Visuals, by Chamara Ranasinghe

A Simple and Fun Guide to Microsoft Flow and Power BI, by Ruth Pozuelo

The Ultimate Waterfall Chart , by Klaus Birringer

Power BI – Germany

I know from personal experience that having your data reside in the same country you are working from means a much easier and broader adoption of Power BI. Now for people living in Germany they are able to fully comply with all the requirements in terms of having data hosted within Power BI.

As a side note I also saw that Microsoft is the first company to offer a data centre in South Korea which is great to see.

You can find the blog post details here: Experience your data with Power BI Germany and meet your compliance and regulatory needs

Power BI – Latest Updates for Power BI Desktop

I recently came across this link below, which always will point to the latest details with regards to the Power BI Desktop.

I would suggest bookmarking this page, to ensure you always have the latest information available.

Power BI – How Microsoft uses Power BI to run its Cloud Business

This is a really interesting blog post where they details how Microsoft uses Power BI to monitor Power BI as well as all the other BI components that fall under James Phillips. What amazes me is that for each of the KPIs shown in the screen there is a General Manager responsible for that particular product.

What this also shows is how to build an effective KPI dashboard which is easy to view and know when something is not going as it should.

You can find the interesting read here: How Microsoft uses Power BI to run its growing cloud business

Power BI – Connecting to the dataset in the Power BI Service

In this blog post from the Power BI team they go into some details around how and why you would want to use the Power BI Dataset in the Power BI Service.

One thing that I found interesting and I guess I had never thought of is that you can now connect to ANY dataset that has been published to the Power BI Service. This includes datasets from Curated Content Packs which makes the development experience that much easier.

You can find the details here: Connecting to datasets in the Power BI service from Desktop

SQL Saturday Brisbane – I’m Presenting on Power BI Real World Tips and Tricks

I am so excited that I will presenting for the first time at SQL Saturday Brisbane on Saturday 27 May 2017.

The session details are Power BI Real world tips and tricks. Below is the session excerpt:

Out in the real world, people are starting to use Power BI to gain insights into their data that were previously never seen. With Gilbert currently working full time in Power BI projects, he has first hand experience on how to leverage Power BI in work place.

By providing real world, real working examples I plan to show how some simple tips and tricks can make their reports and dashboards amazing and insightful. This will be an interactive and demo rich presentation.

The real world tips will cover the following sections of Power BI:

  • Query Editor
  • DAX
  • Visuals
  • Power BI Service

You can register for SQL Saturday here: SQL Saturday Brisbane – 27 May 2017

SQL Server – SQL Server 2017

I personally think that with the release date of SQL Server 2017, that Microsoft is looking to continue what it has been doing with the combination of experience with Power BI and deploying updates to Azure SQL Server.

This is allowing them to deploy and implement changes to SQL Server in at a faster pace. I do also personally think that with the deployment and testing with Azure, they can test real world scenario’s with a diverse environment to make sure that it works as expected. This allows them to then take all these learnings and put it into an On-Premise Solution.

You can find the details here: SQL Server 2017

SQL Server – DAX Editor for SSDT

It was just a matter of time before the DAX editor became part of SSDT (SQL Server Data Tools) and now it is finally here.

This will no doubt make the creation and editing of measures and calculated columns a lot easier and quicker to develop.

They are planning to have Intellisense as well as Code Formatting in future releases

You can find the details here: Introducing a DAX Editor Tool Window for SSDT Tabular

SQL Server – What’s new in SQL Server 2017 for Analysis Services

As you can see above there have been a whole host of enhancements for Analysis Services in SQL Server 2017.

The new Object-Level Security to secure more data looks really interesting in that you can now also secure it on a column level. As well as performance improvements for the developer experience, which I personally know in the past at times was particularly slow.

Along with improvements to the DMV’s, similar Date Hierarchy implementations that are currently in Power BI.

As well as other improvements you can read about here: What’s new in SQL Server 2017 CTP 2.0 for Analysis Services

Server – New Get Data Experience

With the new release of SSDT they have updated the Get Data Experience for SSAS models with level 1400.

It appears that they are looking to align with what is currently being implemented in Power BI, which is fantastic to see because it will mean a more consistent developer experience.

They are continuing to add more data sources with each release.

You can find all the details here: New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

Power BI Dataset tips and potential pitfalls

There has been a lot of hype around the new feature in the Power BI Service where you can connect directly to the Dataset in the Power BI Service

There have also been quite a few blog posts on how to connect and leverage the datasets in the Power BI Service, what I am going to focus on is around the tips and potential pitfalls to be aware of when using the dataset in the Power BI Service, which could make this experience as efficient and beneficial as possible.

Quick Overview of the Power BI Dataset

In my opinion the Power BI Dataset is very similar to SQL Server Analysis Services Tabular Models. Which I have been fortunate to having worked with the Tabular Models for quite some time.

This is due to the fact that you first have to first plan and look at all the requirements
before building your Tabular Model or in this instance the Power BI Dataset.

Without having a solid plan in terms of your fact tables, dimension tables, column names, measure names, hierarchies this could result in having to change the Dataset, which in turn could lead to reports failing or ceasing to operate due to changes being made.

Tip – Changing dataset columns or measures

One of the biggest things that I experienced in the past was when there was a change to the dataset columns or measures. This would result in the report failing to load or the particular visual failing to load.

In the steps below I will show what this looks like when it fails and then how to resolve the issue.

What happens when the dataset column or measure changes

What I have done is uploaded a Power BI Desktop file to the Power BI Service and gave it the name of DATASET – Data Gateway Refresh. This is so that I know that this is my Dataset that I want to connect to and work with.

I have then connected a new Power BI Desktop file to my Power BI Dataset to DATASET – Data Gateway Refresh as shown below.

I then created a simple report and uploaded it into the Power BI Service as shown below called Data Gateway Report

As you can see from above I have used a measure called [Total Value] and a column from my Date table called “Calendar Year“. As well as the overall Total Value which also uses the [Total Value] measure.

Now as explained previously if I was required to change the measure from [Total Value] to [Total] and I did this in my Power BI Dataset Model and upload the changes to the Power BI Service.

So in my Power BI Dataset Model I changed the following as shown below:

To the new measure called [Total]

I then uploaded this to the Power BI Service. I did get prompted to replace the existing dataset to which I clicked in Replace.

I then went into my report previously created Data Gateway Report from the DATASET – Data Gateway Refresh, and clicked on Refresh, and what I got was shown below.

When I clicked on See details I got the following as shown below.

The error description I must say is a lot better than it used to be in the SSAS Tabular Models. As you can see from above it is referring to a field that needs to be fixed. Along with that it is giving you the field name that it no longer can find. And with my example this is called [Total Value].

How to resolve or fix a column or measure name change

The first option to resolve this issue is I went back to my Power BI Dataset and renamed the measure or column to what it previously was.

As with our example I then go back to my Power BI Desktop file where I have the dataset and rename it back from [Total] to [Total Value].

I then re-published the dataset to the Power BI Service.

I then once again go back to my report previously created Data Gateway Report from the DATASET – Data Gateway Refresh, and clicked on Refresh, and what I got was shown below.

This is by far the quickest and easiest way to resolve the issue.

The second and more timely and longer solution is if for some reason you must keep the new measure or column name (could be something as simple as a typo) then what you will have to do is to then re-create the reports again. This can be rather time consuming but I have experienced this in the past and have had to rebuild the reports.

Pitfall – Deleting dataset

A potential pitfall is if I decided for some reason that I needed to delete the dataset. Possibly because there is a requirement for it to be renamed something else, or for some other valid reason.

The pitfall is what when I deleted the associated dataset it not only deletes that dataset, but also the existing reports and dashboard tiles that are connected to that dataset.

As with my example, below you can see the existing Dataset and the report that it connects to using the new feature in the Power BI Service called View Related, which shows us all the related content to the DATASET – Data Gateway Refresh

Now when I go and delete the Power BI Dataset called DATASET – Data Gateway Refresh, I do get prompted with the following shown below.

I have highlighted that it does indicate that it will delete all reports and dashboard tiles.

Once I have clicked Delete, not only does it delete the dataset but also my report previously created call “Data Gateway Report”

How to resolve a deleted Dataset

This could result in a potential issue where users of the existing report can no longer access or view the report. A workaround that I would do is to re-upload the dataset, as well as I would re-upload the Power BI Desktop file which contained the report that connected to the dataset. (As there should always be the original Power BI Desktop file for the Dataset and the Report)


Whilst the new Power BI Service dataset is a great new feature. As well as improve over time. It is also important to know what tips and pitfalls could make this experience as efficient and beneficial as possible.

I would be interested to hear if anyone else has any tips or pitfalls when using the Power BI Datasets?

BI-NSIGHT – Power BI (Service March Update – New Navigation Experience, Desktop Terms & Definitions – Whitepapers) – BI Survey (BARC BI Survey 2017) – Excel – (Get & Transform Updates for April 2017)

There has been a variety of updates and blog posts in the past week, so here are my BI updates.

Power BI – Service March Update

There were quite a few service updates for March within the Power BI Service.

The first was the granular controls which was a very welcome feature. And I know that something that a lot of companies have been asking for. (As well as where I am currently consulting)

Another great new feature was the capability to view related content. This gives the user the ability to see how their content is related, as well as to quickly go into additional items from the view related content.

There was also an improvement for the troubleshooting for DAX queries, which helps with the diagnosis of errors or issues.

Then there was the custom scheduled cache refresh which is really handy in terms of how to handle this for DirectQuery sources.

As well as the ability for Amazon Redshift, and finally the move of the Custom Visuals into the Office Store.

You can find the blog post here: Power BI Service March Feature Summary

Power BI – New Navigation Experience

I have enabled the preview features on Power BI for quite some time, and I have to say with each iteration it has gotten easier and better. The new navigation experience is awesome. And makes interacting with Power BI in the service a lot easier. This is especially true when you start to have multiple workspaces, compounded with multiple dashboards, reports, workbooks and datasets.

This new navigation experience makes it a lot simpler to use and navigate.

You can find the blog post details here: Get ready for the new Power BI navigation experience

Power BI – Desktop Terms & Definitions

There is a really insightful blog post by Sam Lester from Microsoft where he goes into the terms and definitions when using Power BI Desktop.

If someone new is starting out in Power BI and using the Power BI Desktop this is a great place to start.

You can find Sam’s blog post here: Power BI Desktop – Terms and Definitions

Power BI – Whitepapers

There is now a central location for all the related whitepapers for Power BI.

This will be really handy because it will be easy to reference this for customers and clients going forward.

As well as I enjoy reading and learning from released whitepapers, so this means that I now have a central location for some great reading material.

Here is the link to where you can find the whitepapers: Whitepapers for Power BI

BI Survey

Every year I have been working in the Business Intelligence space I have been participating in the BARC BI Survey, and due to being a participant I get a copy of the results once completed. I find this invaluable to see what is happening in the BI space.

If you are interested please follow the link to complete the survey: BARC BI SURVEY 2017

Excel – Get & Transform Updates for April 2017

It is great to see the changes and updates that are made to Power BI filter down into the Excel Get & Transform.

In the April update they have included the following updates:

  • Support for the same file extensions in Text and CSV connectors.
  • ODBC and OLE DB connectors—support for Select Related Tables
  • Enhanced Folder connector—support for Combine Binaries from the Data Preview dialog
  • New Change Type Using Locale option in Column Type drop-down menu inside Query Editor
  • New Insert Step After option in the Steps pane inside Query Editor

You can read up about it here: April 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Free vs Pro Infographic

I have been active on the Power BI Community Page for quite some time, and what I have often seen is people not quite sure what options are free and what options require a Pro license.

So here is my infographic in which I have put down which options are free and which options will require a Pro license.

I am hopeful that people will find this useful in understanding which options are free and which options require a Pro license.

If I have left anything out, or something is wrong please let me know and I will update it.

As well as I will keep this infographic up to date as there no doubt will be some additions to the Power BI Service.

















You can access the images from the following link: Power BI Free vs Pro Infographic – Latest Version

BI-NSIGHT – Power BI (Desktop Update April – Quick Measures Preview – Mobile Apps Summary – Video Gallery & Ask a Partner Anything – View Related Content Pane – Sneak Peak add new Columns from Example – Tracking Adoption via Power BI Audit Logs)

There has been a whole host of updates and interesting things in Business Intelligence in the past weeks, so please find below the updates.

Power BI – Desktop Update April

This month’s Power BI Desktop update for April has a whole host of great updates and new features this month, which I will highlight what I think is most relevant below.

Report View

The first update in the Report View section is the ability to rename the axis titles. I know that personally this is something that I have been looking for and now the capability is there.

Next is additional updates to the Matrix Preview, in which you can now sort the matrix by the grand total and row headers in ascending or descending order.

As well as now you can also resize the columns as you could do previously in the original matrix.

And finally, you can now word wrap column headers, row headers and values in the matrix preview. This can be configured separately in the formatting pane.


Not only has the Power BI team made it better and easier to create the default measures. But with the Quick measures they have made it as simple as a drag and drop and then Power BI Desktop writes the DAX for you. I personally think that is a giant leap forward as it enables users who are not very strong in DAX to still be able to create powerful measures, which in turn will create greater insights into their data. This will also then let people learn how DAX works because they can see and modify the code, as well as give more advanced DAX users the capability to edit the DAX measure if so desired.

This entire preview feature I think is another game changer and it will enable people to quickly create the measures that they require, as well as to get people to build their DAX skills if so required. And I have no doubt that more quick measures will be added.

As you can see above, you can now try Q&A in Spanish. This must be the result of the Power BI Surveys that have been conducted in the past.

Data Connectivity

Once again this is another idea that a lot of people have been asking for and it has already been delivered. This is the ability to connect to a dataset that exists in the Power BI Service.

This is wonderful as what it means is that as long as you have got the required access, you are now able to access a dataset already uploaded into the Power BI Service. So what this also means is that you will not have to worry about creating measures, how the data is updated etc, because you are a consumer of the Power BI dataset.

This also means that now you can have multiple people working on a single dataset. Which is the start of having multiple developers developing reports and insights into your data.

The one caveat is that when you use the Power BI Service dataset you cannot edit or modify anything within this dataset, you will have to get the dataset owner to make the required changes and then for them to upload it the changes or updates into the Power BI Service. But still a giant leap forward.

The Amazon Redshift data connector has now moved out of Preview and is in the Beta phase, so it is easier to find and use.

There has also been updates to the SAP Hana and BW connectors, now giving you the ability to have more control with regards to the parameters selection. I am sure that people that use SAP will find this very welcome.

Query Editing

The ability to add a column by example has been out for almost 2 weeks now, but this is once again (yes I am repeating myself a bit!) a game changer because it now allows people who do not understand Power Query or the M language to quickly and easily build new columns within their dataset. And this also does it in the Query Editor, where I personally think it is the right place for this to be done, so that before it is brought into the Power BI Desktop Model, the data is already there and can achieve better results.

As you can now see above you now can split your column by delimiter or number of rows and if you wanted to split it into Rows.

The basic Group By in the Query Editor now allows you to group by a single column and output by a single column.

The Go to Column is a very handy feature because it allows you to quickly find your column, which is especially helpful when your table is very wide with a lot of columns.

You can find the blog post for the Power BI Desktop Update here: Power BI Desktop April Feature Summary

Power BI – Quick Measures Preview

There is already a blog post out by the Power BI team with regards on how to leverage and use the Quick Measures Preview.

It has some great content and is well worth the read.

You can find the blog details here: Quick Measures Preview

Power BI – Mobile Apps Summary

There have been some additional updates to the Mobile Apps for Power BI which are the Q&A Improvements and 3D touch for iOS.

Multiple SSRS Server support for up to 5 SSRS Servers. As well as improvements to the slicers.

You can find all the blog details here: Power BI Mobile apps feature summary – March 2017

Power BI – Video Gallery & Ask a Partner Anything

As you can see from above the Power BI team has now launched the Video Gallery where you can view tips and tricks on how to do things with Power BI. I think that this is great because it is often easier to see how to do things.

As well as another Ask a Partner anything on 06 April.

You can find all the details here: Announcing the new Power BI Video Gallery and Ask a Partner Anything live event

Power BI – View Related Content Pane

Once again the people in the Power BI team, specifically on the Power BI Service side are making it a lot easier to navigate and understand how your reports piece together.

This is very powerful as well as easy to use, because you get to see all the related content, but you also get the capability to be able to go into the settings or item specific areas, where before you would have had to go through a few clicks to get there.

You can find the blog details here: Announcing the View Related Content Pane: Faster Results with Fewer Clicks

Power BI – Sneak Peak add new Columns from Example

As you can see from above, this is a sneak peak of a new feature that is going to be released in the next version of Power BI Desktop.

I have to say that this is amazing, as it is starting to do the hard yards for you. Yes it might take a bit of practice to get it right, but once you have mastered it, it can save you a lot of time and effort

And the thing that I really like is that it will enable the users who are not so proficient at Power Query to be able to expand on their data, which in turn will lead to amazing insights.

You can read the blog post here: A sneak preview of the new Add Column From Examples data transformation

Power BI – Tracking Adoption via Power BI Audit Logs

This is a great blog post from JAVIER GUILLEN, where he shows you how to use the Power BI Audit logs to show how adoption of not only Power BI, but also the dashboards and reports are being used.

You can find the blog details here: TRACKING ADOPTION VIA POWER BI AUDIT LOGS

SSAS / Power BI – DirectQuery WhitePaper

As you can see above there is a whitepaper for DirectQuery in SQL Server 2016 Analysis Services, it is noted that some of the concepts are shared with Power BI.

And I have already downloaded the whitepaper and will be getting stuck into it very shortly.

You can find the blog post details as well as the link here: DirectQuery in SQL Server 2016 Analysis Services whitepaper

Power Query – Adding Parameters within a piece of text

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


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


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

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

#”Budget Version”

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

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

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

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

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

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

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

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

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

Final Note

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