PowerBI.tips

Author: steve campbell

Power BI Support Channel: Microsoft Teams Tips

When organisations deploy Power BI, it is important to offer support to users. Often this can be in the form of formal help desk and tickets. In addition to this, many companies use Microsoft Teams to set up specialised support channels. Teams offers a quick and straightforward way for users to get answers to questions. However, best practices should be followed when setting these up. This article focuses on some communication tips in Microsoft teams to help create a successful support channel.

Additionally, we recommend reading the Power BI Adoption Roadmap. This roadmap goes into detail about rolling out Power BI and setting up user support. It is a vital part of creating a data culture within your organisation.

Use a Power BI support channel

It can be beneficial to have an individual location for users to ask for help and support with Power BI. Typically, there will be a dedicated set of users who are knowledgeable and proficient with the tool. Some organisations give them nicknames such as champions, super users, or rockstars. In this article, we will refer to them as the BI team.

An organisation can, and should, set up different teams for individual projects. As part of a Centre of Excellence (CoE) or a User Support Hub, there may also be a team specifically for Power BI or the Power Platform. This specialised team would have channels dedicated to supporting and assisting the wider organisation.

To implement this correctly, the BI team should encourage users and ensure awareness of the correct teams. For example, there may be a project team working on a problem involving Power BI. After some deliberation, the project team gets stuck and decides to ask the help of the BI team. One of the project team members collects the relevant information they head to the support channel, which could be part of the CoE. Posting in this correct support channel, rather than the project channel, allows the project team to get answers quicker.

A centralised Power BI support channel

Having a centralised Power BI support channel depends on organisational structure and may not suit all businesses. However, there are several advantages for a centralised channel. There is a centralised area for the BI team to focus on, instead of users asking questions in multiple areas. A central location can also encourage users to collaborate and ask questions, helping adoption and learning at the organisation. Other users can see the results and responses, which may answer their own issues without need of additional help.

Additionally, the BI team members do not need to be added to multiple teams to support every query. This also reduces the BI team from receiving large amount of support emails, or direct contact from users asking for help. If users email and speak to BI team members directly for support, the BI team should politely tell them to ask the question on the correct channel.

The BI team should include a wiki on the channel. This can have helpful information about the BI team itself.

Asking support questions

A support channel can be invaluable to a business and be a great alternative to a help desk. However, even if it is not an official ticket, thought and structure should be put into the question. Lack of information can cause wasted time for the BI team to figure out exactly what the need is. This goes against the goal of getting users a timely answer. It will also have a knock-on effect as other questions will take longer to be answered. Additionally, it is often a source of great frustration to the BI team when there is not enough information to provide a response.

One effective way to aid users is to create a question template. Place this in the wiki of the channel and pin a message in the channel chat telling users to visit the wiki, so they can easily find it. Still, even with advice, many users may ask poorly formulated question. In this case, it can be easy for the BI team to respond negatively. However, this is not useful and may result in users not reaching out for help. Instead, the BI team should remind the user how to formulate a question and point to the template. If necessary, they can de-prioritise questions that do not use the template while prioritising those that do. Having your question prioritised, and therefore answered quicker, is a big incentive for people to ask questions in the desired format.

Example Response

Question Template

Here is an example of a template you can supply to users. When users have a question or issue, they are expected to copy the template and answer the headings as required.

Business need
Give some context on why this is needed to be looked at. If it is affecting reports or datasets, say what they are and who uses them.

Issue
Go into detail and explain what the issue is. List out anything you’ve tried before.

Expected behaviour
Explain what you would like to see and the output you were expecting.

Code
Use the code snippet function to add any code examples. Please format it first, using tools such as DAX formatter.

Link (optional)
If this relates to questions or messages in other teams, you can link to them here. You can copy a message link by clicking the ellipsis on the message. Alternatively, you can link directly to the channel the same way. Note that the BI team member will need to be added to the channel to see any messages.

Attachments
Attachments are a great way to show the trouble you are having. Include examples where you can, and try to include data where possible

Tag lists

A feature called Tag lists allow you to mention specific people. This helps users make specific people aware of the message, while not tagging a channel directly, which can be frustrating to those in it. Tag lists are great for alerting the correct people to a problem, without sending unnecessary notifications to others. An example could be tag lists could be “DAX Experts” within the BI team. This group contains those who are experienced in DAX. When users need DAX help, they can @ tag that list. The people in the list would get a notification that a high priority issue has been raised. Now only the DAX experts get notified, as they are the ones who can respond to the query.

Tag lists as notifications

Other uses for tag lists are opt-in notifications. Let’s take an example of a fictional company. The company creates a team called “News and Announcements”. Here, they post valuable news and information about Power BI. The company needs to follow the monthly release of Power BI, as software downloads are tightly controlled. Some users wish to know as soon as a new release is announced, while others are not as interested.

First, they create a tag list named “Power BI Release Alerts“. Users who want to be in this tag list can opt in. Now, whenever there is a monthly release of Power BI release, a designated user will post an announcement in this channel. The poster tags the “Power BI Release Alerts” tag list in the post. This allows users to sign up for alerts and get notifications about the announcement. Others who do not need to know as quick can still find the information but view it in their own time.

The tag lists should be an opt in group. The organisation size and structure will affect how to manage this. Some organisations will allow users to assign and manage tag lists, and to add themselves to the appropriate lists. Others may have a formal request process set up to be add or remove a user from a list.

Use a hierarchical folder structure

Within your files section, be careful how you arrange documents. Take time in creating a logical folder structure. A new user should be able to access the files and understand the logic of the folder structure.

Hierarchical folder structures rely on various levels. The top levels are used only for navigation and keeping the file structure clean. Direct files should be saved in lower down folders. This stops users adding multiple and unrelated folders, or saving files in the root folders.

An example of a hierarchical folder structure:

1st Level: High Level Subject Areas
2nd Level: Subjects
3rd Level: Breakdowns
4th Level: Individual files saved inside level 3 folders

Best Practices in Communicating on Teams Power BI support channels

When using the Power BI support channel, reply to the correct conversation rather than starting a new one. This will keep the channel tidy and the responses will have context. When starting a post, set a subject like you would an email. You can do this by clicking the format button under the input box (shown in the image below).

Format button

Only if necessary, notify people that are the recipient of the message by tagging. You should think carefully about tagging people. Tagging someone implies there is a need for them to read the post urgently. If you are asking a question in a support channel, it is unlikely you need to tag someone, as they will be reading the channel when possible. When possible, tagging should be reserved for tag lists, to allow correct users to be notified. Additionally, always avoid tagging the team or channels directly. There is no need to notify everyone, as they are part of the team anyway.

Use chats for conversations that do not require outcomes. Chats are good for working sessions, informal talk or quick questions. In many cases it can be helpful to mute chats to stop alerts distracting you do not participate in it often. Try and keep conversation in the appropriate Channel if you need a quick response from someone.

If you do use chats, follow the no hello rules. Don’t start a chat with just “hello”. Feel free to add a greeting, but include why you want to speak to that person too!

When creating Channels, make use of the apps. There are many different apps you can add, or create your own in Power Apps, such as the Power BI Version Control app.

April 30, 2022
8 Mistakes I made to Start Learning Power BI

If you want to start learning Power BI, or are looking to improve your skills, there are lots of areas you can start. However, learning itself is a skill. When I started out, I had a few things that slowed down my progress. These are eight common mistakes that I made when I started my journey of Power BI, and the lessons I learned along the way!

1) Trying to Focus on Learning too Narrow

There is a lot of areas to cover in Power BI. Most people see the visual design side of Power BI (being graphs and visuals). Yet, there is much, much more. Power Query and DAX are two detailed languages to learn in themselves. Next, there are tabular models and analysis services. Then you have the Power BI service, governance, and all things that come with software deployment. On top of that, all the third-party tools and add-ons.

You do not need to master these, but you should have breadth in your learning. Understanding the fundamentals and basic concepts of each stage in Power BI. When starting out, I dived into Power Query and wanted to be an expert modeller. While this is not a bad idea, I neglected some basics of database design and governance. Without these, my skills were not complete enough to be considered an expert in any one aspect.

Lessons Learned: See the bigger picture and start with the fundamentals. Don’t stop being a specialist if wanted, but get the basics covered first. Look at the skills matrix. Try to get to at least level 4/5 in each subject, then dive into the detail.

2) Trying to Focus on Learning Everything

A warning to the previous post, is to try and master all the areas. As I learned more, I thought learning Power BI required me to learn everything about it. Yet this was an unrealistic task. When I went to start learning Power BI, I tried to master Power Query, DAX, Analysis Services, design theory, governance among many more. The result is that I was taking on too much and forgetting most of what I had learned.

Lessons Learned: Find your niche. You may love story telling, enjoy writing complicated Power Query or advising users on admin features. It is a careful balance to not focus on too narrow or too broad. Learning is an iterative process though. To find an area you excel at and enjoy the most, you need to experience more. First, learn the fundamentals and get a good breadth of knowledge. Next, get involved in your favourite areas and get a good depth of knowledge.

3) Saying YES to every request

This is one of the biggest mistakes I made (and see others make) when taking requests. Stakeholders requesting reports are often less versed in Power BI. As a result, they may not understand best practices or visual design theory. It happened a lot – they would ask for unique and specific requirements. I took it as a challenge to try and deliver exactly what they wanted. This often-meant creative hacks, crazy DAX, and bizarre uses of visuals.

While I was pleased that I could stretch Power BI to meet users’ needs, I know now this was a bad approach. This resulted in hours of fixing the complicated visuals when things went wrong. Changes that should be small turned into complex projects.

Leason learned: You are the expert for a reason. Knowing how Power BI should be used is an important skill. Being able to explain this and not over customizing reports is even more important. Learn how to take requests from users and understand what they really want. The story the report tells is much more important than the style of visuals on a page.

4) Not Understanding enough about I.T. practices

I came from a business side analytics role. I was a big excel user and loved creating complex and unique solutions to solve difficult problems. However, I did not know enough about I.T. best practices. These include DevOps, releases, or multiple environments (e.g. DEV, TEST, PROD).

While I could develop reports, I did not know the processes to deploy them. I did not know how to thoroughly test.

Lessons Learned: While you do not need to be an expert, you should know the basics. When you start learning Power BI, make sure you spend a bit of time understanding these. Moreover, contact your I.T. department and ask about the practices in your organization.

5) Start Learning Power BI too advanced – not at the fundamentals

I love learning new skills. The more complex, the better. Trying to learn the most difficult and complex tasks seemed like a clever idea to quickly advance and become a pro. Yet, the reality is a little different. You may have heard the phrase “Don’t run before you can walk”. By diving in too quickly to the complex tasks, I missed some fundamental knowledge. This slowed my learning down overall. Without that knowledge, I found it difficult to grasp the why. This doesn’t just apply to skills in Power BI Desktop, but fundamentals in databases and data transformation too.

One area I did this is when I was learning to data model. I tried to learn all advanced methods and specialised tricks. I would find complex ways of doing powerful tasks. But without the fundamentals, I wouldn’t fully know the why. I learned replacing long text keys with integers was a good idea. Learning about data storage and the reasons behind this helped me know why to do this and understand the best methods.

Lessons Learned: When you start learning Power BI make sure to learn the basics. Even as a seasoned developer, make sure you revisit them. Learn the fundamentals of skills such as databases or data storage. Learn about STAR schema, keys, dimension, and fact tables.

6) Going too fast in learning DAX

At the start, DAX seemed like the holy grail. It was familiar from an excel background. So, I set off to master it. It seemed simple enough to do.

I was wrong. DAX is hard. In fact, DAX is extremely hard. Many beginners will say DAX is easy. Only until you learn DAX, you know it is not. While it may be easy to do some calculations, others can seem impossible. Writing DAX requires a knowledge of how it works. DAX also works differently to anything I have used before.

The worst scenario is not knowing DAX’s difficulties. You may end up writing incorrect DAX, and not know it is wrong. Users will consistently come back saying the numbers are wrong. Eventually, people will stop trusting your reports. Luckily, you do not need to be an expert in DAX. You can be a great developer will reasonable skills.

Lessons Learned: If you are struggling, first make sure you model is following best practices. This is the often cause of most DAX issues. If you want to be an expert, then great. This can be a valuable skill when you start learning Power BI. Otherwise, ensure you learn and re-learn the fundamentals. Read this article on the what the fundamentals are. Educate, practice the skills, then re-educate. Never stop learning DAX, and never stop visiting the fundamentals. Read this amazing article by SQLBI.

7) Start Learning Power BI by Ignoring best practices

Learning new skills is fantastic and often I was eager to show them off. The more complicated an answer was, the more I wanted to include it in a report. This gave me great confidence and made me feel like I really knew the subject.

However, this rarely translated into better reports. What I found was changes became difficult to make. Additionally, reports would break in the future. Minor changes and growing data would often mean repair was needed for the reports. Ignoring modelling standards such as STAR schema resulted in hours spent in DAX.

Lessons Learned: Learn the best practices and religiously follow them. Microsoft have an excellent resource for this. Simple is often better – you want to design reports that are robust, and users can trust will not break.

8) Start Learning Power BI without asking for help

There are lots of areas you can start with in Power BI. To be a great developer, it takes knowing lots of different skillsets. While there is a great deal of paid training, this may not be available to everyone. Self-training is an effective way to learn, but everyone will need some help sometimes. Thankfully, the Power BI community is an outstanding community. There are some amazing people who spend their time helping others. Yes, it is important to learn by making mistakes and figuring things out on your own. However, if you are really stuck on a problem or concept, reach out for help before getting slowed down or frustrated.

Lessons Learned: Knowing how and where to ask for help, however, is important. Even if someone likes to help a lot, sending unsolicited messages to people you do not know can come off rude and annoying. Luckily, there are a number of places you can go for help:

Community.PowerBi.com – This is the first place to go for specific questions when stuck. Please don’t treat this as a place for free consulting, but rather to help you understand concepts you are stuck on.

User Groups – These are great to join and attend monthly meetings. They normally have a local community that include all levels of knowledge – great for learning and asking questions.

Social Media – There are a number of active communities. There is a great Reddit group and some active users on Twitter. In addition, LinkedIn has many users who love to share knowledge (feel free to follow myself or PowerBI.tips too!).

Conclusion

It can sometimes be overwhelming to know where to start in a new skill. There is a phenomenon called the Dunning-Kruger effect. This is the effect that often, when you start learning, it will feel easy. However, as you learn more, you realise how much knowledge there is and feel there is so much you don’t know. Please, do not let this put you off. This is a very common feeling that almost everyone goes through.

Instead, focus on getting great at the basics. Keep it simple and to things you can complete. Reach out, join the community, and ask questions. Of course, most of all, have fun!

February 10, 2022
Power BI Bookmarks Tips, Tricks, and Best Practices
This article describes Power BI Bookmarks tips, tricks, and best practices. Bookmarks are a powerful feature that can greatly improve the reader’s experience. However, there are several settings you should be aware of. Used incorrectly, they can become hard to maintain and often not display the intended functionality.

Power BI Bookmarks

This article will assume some basic knowledge on how to record or apply bookmarks. Please see this. If you are new to Bookmarks, you may want to watch this helpful video by Adam Saxton from Guy In A Cube.

It will run through an example of using a switch visuals bookmark group. If you have not seen this before, I suggest you watch this video, also by Guy In A Cube.

You should be familiar with:
The rest of the article will focus on some tricks and tips using bookmarks, while walking through an example of a visual switch between a map and a graph.

Tips Covered
Change Power BI Bookmark Settings

You should always update the bookmark settings when creating them. This should always be as specific as possible to the action they are trying to achieve.

Here is a quick breakdown of the settings:

Data: This captures items including filters and sort order. It does not capture if the item is visible or not.

Display: This captures whether an item is visible or not, without modifying filters or sort order.

Current Page: This will switch to the current page view if you apply the bookmark from a different page. If unselected, it will still apply the bookmark, but it will stay on the page you apply it from.

All Visuals: If this option is selected, it captures every setting on the page. This can include items in the filter pane, or even if the filter pane is open. I would recommend to never use this setting.

Selected Visuals: Selected visuals still only apply bookmarks to the visuals you had selected when you record the bookmark. You can select items by holding control and clicking on them in the selection pane.

In general, I try not to use both Data and Display together, as most cases bookmarks are just changing one of these fields. Previously, it was required to use bookmarks to navigate pages. However, this is no longer needed after new functionality allows this to be done with buttons directly. Therefore, I rarely use this feature.

One of the best Power BI Bookmarks tips – use selected visuals only. Recording bookmarks on all visuals often has unintended consequences and can be difficult to manage. Rarely is this needed and can become very hard when adding more visuals to the page.

Rename Your Visuals

The first tip is to rename your visuals. Each visual is given a name, which can be viewed in the Selection Pane. By default, the name is usually the type of visual it is. This means if you have several slicers on a page, you might get a list of several visuals with the same name. While this is not an end user feature, it can make it difficult to identify the correct visual when developing.

We recommend renaming each visual when you add it. First, open the selection pane. Next, double click on the visual you have just added in the selection pane. Rename the visual to something that calls out what it is. Our recommended naming strategy is the following.
```
Visual - Description
```
Example of renamed visuals in a selection pane

Pro Tip: When a page contains multiple bookmarks it’s difficult to know what Bookmark touches which Visuals or Groups. Thus, when you are planning multiple bookmarks on a single page add an ID at the end of the Visual or Group. This will correspond to a number listed at the end of the Bookmark.

Record Power BI Bookmarks on Groups

Using groups has huge benefits for Power BI bookmarks. If you record bookmarks on groups instead of individual visuals. Now, any edits made on the content of the groups will flow through, without the need to re-record bookmarks.

For example, let’s say I want a bookmark that switches a visual from a table to a map.

First, I’m going to make the groups. Open the selection pane. Whilst holding control, click on each visual that should be in the group. Right-Click on one of the selected visuals, then click the list option named Group then in the sub menu Group. You should also rename the group, so you know what it contains.

Note that to set up a group, you need at least two visuals. In my example, I have a graph visual and a title. If you have just one, you can still set up a group. Simply add a blank text box or shape and group it with your visual. You can then delete the blank text box or shape and the group will persist.

HINT: Elements can be difficult to move or select after grouping. If you want to modify a visual, use the selection pane to select it easily. If you want to move it, click and drag the ellipsis to move it.

Add Placeholder Groups to Easily add More Visuals

Next, I will set up the remaining groups. Aside from the map group, I’m also going to add some placeholder groups. To do this, I will add a blank visual and a blank text box.

Next, I’m going to select the new group. Then I’m going to copy and paste using Control-C and Control-V to create three placeholders.

Now that all the groups are set up, it is time to record the bookmarks! For each group follow these steps:
1. Using control, select all visual groups including the placeholders.
2. Using the eye, hide all visual groups except the Map – Visual Switch.
3. Rename the bookmark in the bookmark pane by double clicking it.
4. Click the ellipsis to open the bookmark settings.
5. Deselect Data and Current Page. Change to Selected Visuals. The settings should look like the picture above.
Rename and Group Bookmarks

Two more Power BI Bookmarks Tips are to rename the bookmarks and to group them. In addition to renaming your visuals when adding them, you should also rename your bookmarks. For the bookmarks themselves, I do action name – function.
Secondly, you should group similar bookmarks together. For example, the bookmarks in the visual switch should be put in the same group. This group can also be renamed. I often like to include the page name in the group and then its function.

Example of Power BI bookmark groups and names

Pro Tip: You will want to provide a connection between the bookmarks and the visual elements on the page. By adding an ID at the end of the Visual or Group and the Bookmark you can create a traceable link. This is especially important when you have multiple repot developers working on the same report. Adding an ID signals to the next report developer that these bookmarks are influencing the associated items on the Selection pane.

Layers

For this section, you should be familiar with the selection pane. Remember that objects at the top of the selection pane are in front of those below it.

Now that we have our groups, it makes it simpler to have buttons. In my example, I will create a button that says Graph, plus a button that says Map. If we are switching visuals, it is useful to have highlighted what visual is presented. I will highlight with a blue background and bold text.

One way of doing this, is to layer text boxes behind the buttons. First, create the buttons at the that will contain the bookmark action. This button will be see-through and slightly larger than the text boxes.

Next, create the middle text boxes that formatted for the selected button name.

Finally, create the back text boxes that are formatted for the unselected button name.

Example of the button elements

Keep it Tidy

Once we have repeated for all buttons, we can tidy it up. First, place all button elements in the same position on the page. You can do this easily using the align function.

Then, place the middle text box inside the group it relates to. This means when the bookmark is applied, the selected format will be visible for the correct visual. The trick is that we will layer the elements, so this text box will appear in front of the unselected text box. The buttons will always be on top, so the functionality will always remain the same.

Group the front buttons together, and make sure they are in front of the text boxes. These control the functionality of the Power BI Bookmarks.

Group the back text boxes together, and make sure they are at the back.

The layer sets:
Visual Switch Buttons: These are the buttons that apply the bookmarks. This is the top layer and always visible. This contains the Front Buttons.

Visual Switch Groups: This is the groups we set up earlier. This contains the text box that shows the highlighted button name. As the text boxes are part of the groups, only the selected one is visible. The is the middle layer. This contains the middle text boxes.

Button Text Boxes: This contains the text boxes that go at the back and show the unselected value. This contains the Back Text Boxes.

Final Words

Out of all the Power BI Bookmarks tips, the one I would stress the most is: use the selected visuals setting. It will make your reports much easier to maintain!

If you are not convinced to use bookmark groups, it was recently announced at MBAS 2021 some extra functionality. This will allow users to apply whole bookmark groups to a page, automatically adding buttons when you add extra bookmarks.

I also described a similar tip using buttons almost two years ago. This uses slightly more advanced technique, and some functionality was not out then that is now. However, it is still valid and a viable option.

If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat
June 22, 2021
Power BI Version Control

Please navigate to the following page to see Power BI Version Control:

https://powerbi.tips/2021/02/power-bi-version-control/

January 1, 2021
Power BI Architecture in a Data Solution
This article will focus on Power BI architecture within a data solution.
In this context, Power BI architecture describes how Power BI can slot in as a piece of this strategy. For instance, this includes not only the reports, but the data retrieval, storage and machine learning involved. Next, it discusses different roles and responsibilities involved. This can expand on Power BI skills, looking at the entire solution.

In addition, we hope it to provide ideas for current developers looking to expand Power BI skills or change directions in their career. It can provide a look at areas of need in organizations and give thought of learning opportunities available.

Power BI is Greater than a Report

In my previous article, I discussed how Power BI should not be thought of as a separate product to ETL, AI/ML or overall data strategy. Rather, organizations need to include Power BI architecture as part of a data culture with all of the products working in union.

As a recap from the last article, a modern data platform typically has 4 steps:
- Load and Ingest – extract the data out of the source system and transform it.
- Store – Land this data somewhere so we can run analysis on it.
- Process (or transform) – Run analytics on your data and draw out KPIs, AI and predictions.
- Serve – present this data in an easily way for stakeholders to consume it.
Medium Size – Power BI Services

It is possible to implement a reporting strategy entirely in Power BI. First, we can load data using dataflows. Next, these can be stored as a dataflow in the service or backed by Data Lake Gen 2 storage. It is good practice to separate our Power Query models and reports.

Large Size – Azure Services

Sometimes, we want to use more services than just Power BI. This may be due to huge datasets, use of data in other applications, or writing custom machine learning. The above diagram shows an enterprise scale reporting solution. Azure data factory can move and transform the data. Afterwards, we can store in a variety of storage options, depending on the nature of the data. Many options are available to run machine learning on the data. This ranges from custom code to autoML. Lastly, we create data models and we can produce reports off them. This integrates the Power BI architecture into a whole reporting solution. There is also a path for streaming data – through Event hub and Stream analytics.

Azure has a host of services available. If you are new to these, it can seem a lot to learn. Luckily, Microsoft are rolling out Synapse! This includes a portal that houses many of these services, enabling you to use them all in a single place. If interested, Nicola Ilic I has a great series on Synpase and Power BI.

Different Roles in a Data Solution

If we want to design a data culture, we often need more roles and skills than just designing reports. The below list looks to identify some different roles and responsibilities in a data solution. This is not intended to be a fully comprehensive list. Rather, we explore some different and common roles that could be involved within a project.

It is unlikely a single project will require all roles. Usually, one person may take on two or more of these roles. Instead, the aim is to distinguish different areas of the data strategy. This can help us to view Power BI in the bigger picture, seeing where it fits in.

We will look at the following architecture – a company that uses Synapse as well as dataflows. For simplicity, we are not looking at any streaming reports. In this diagram the dataflows storage is implied to make the diagram easier to understand.

Each role will look at some common responsibilities and skills. It will also highlight the area in the above architecture diagram that they are responsible for.

Power BI Developer

The Power BI Developer is responsible for building and owning data models for KPI and user reports. Therefore they will spend large parts of their time modeling and transforming data in Power Query or dataflows. In addition, a BI developer has good understanding of tabular models and how to write custom business logic in DAX. They may also be required to set up the Power BI architecture.

Skills

Expert in Power Query and DAX. Familiarity in tabular editor and DAX Studio. Plus knowledge on database designs and tabular modeling such as implementing good STAR schema. Great Power BI skills and ability on Power BI desktop.

Roles of a Power BI developer

UI/UX Engineer

In larger projects, report design can benefit from a UI/UX specialist. Power BI developers will often complete this, but specialists can be brought in to help design layouts, flow and brand consistency within the project. Usually, the Power BI developer will look after the models and logic. However, the UI/UX Engineer helps make sure the final reports are professional looking. In addition, they can be involved in storyboarding the design of reports. A UI/UX specialist is needed for projects with many reports, external facing reports or embedded solutions.

Skills

Design skills as in products such as Adobe Illustrator. Dashboard design (such as gestalt principles).

Roles of a UI/UX developer

Data Engineer

A Data Engineer is responsible to get the data from the source and load it into Power BI. Smaller reporting projects may use only dataflows or power query, but larger ones might require more steps. The Data Engineer will move the data using tools such as data factory into a SQL database or Synapse storage. This allows larger enterprise solutions with massive volumes of data, or for complex machine learning to be performed on the data. In addition, Data Engineers will transform and clean the data making it suitable for reporting analysis or machine learning. They will then integrate this back into the solution.

Skills

Tools such as Azure Data Factory, Stream Analytics, Data Lakes or other data storage. In some projects a Data Engineer might work exclusively with dataflows or Power Query. Databricks, Spark Analytics and SQL are important for prepping and transforming big data. Engineers also can benefit from automating in languages such as PowerShell. Strong Power BI skills.

Roles of a Data Engineer

Data Architect

Data Architects are responsible for designing, managing and maintaining the reporting solution. Architects will suggest the best selection of tools and methods used. They would be the ones to recommend the team involved, the technologies used and the correct approach. Overall, they will set up the Power BI Architecture and machine learning solution.

Skills

Architects need to have vast knowledge and experience across all the technologies that can be used. This includes deep knowledge on Azure or Synapse, Power BI and data governance methodologies. They have solid understanding how machine learning can be integrated into a solution.

Roles of an Data Architect

AI Engineer

You may incorporate an AI Engineer in large projects that have a machine learning focus. Data Scientists spend large amounts of time writing custom algorithms. In contrast, AI Engineers use tools such as Azure cognitive services or Azure machine learning studio. On large projects they may work in tandem with Data Scientists helping to merge their code into the reporting system. They often spend time cleaning, transforming and prepping data. In many cases, this job is regularly undertaken by Data Engineers. However, projects with a heavy AI focus may benefit on having an AI Engineer or both a Data and AI Engineer.

Skills

While AI Engineers may not write custom algorithms from scratch, they need a solid understanding of machine learning principles. They also need to know how to prepare and clean data ready for machine learning solutions.

Roles of an AI Engineer

Data Scientist

The data science field has exploded in popularity over the recent years. Typically, a Data Scientist will spend their time cleaning, prepping and analyzing large volumes of data. Next, they write custom algorithms that can detect deeper insights. A Data Scientist often has years of experience and training coming from various backgrounds. Data Scientists write custom code in Synapse, Databricks or Apache Spark notebooks.

Skills

Expert in an analytical programming language, typically R or Python. They will have a unique blend of programming skills and statistics. Deep knowledge of designing and implementing different machine learning algorithms. In addition, they will be proficient at cleaning and preparing data.

Roles of a Data Scientist

Conclusion

As we can see, there can be many different roles involved in a data solution. Many times, one must wear many hats. Other times, organizations can benefit from having several specialists in different areas.

Microsoft is looking to unify the Power BI architecture and overall data solution through Synapse. This portal will make these roles easier to be completed by fewer people within the same portal. Still, there will always be a need for specialists. So if you are looking to expand your power bi skills, or finding new areas to expand into, make sure you familiarize yourself with these.

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
December 13, 2020
Power BI is part of the greater data solution
Power BI is a powerful reporting tool that has been dominating the market and rapidly evolving. Yet, in many organizations people seem unaware of its true potential or core purpose. As a result, too often it is deployed to simply extract or visualize data points in an ad hoc reporting manner.

Power BI is greater than a report

Power BI should not be thought of as a separate product to ETL, AI/ML or overall data strategy. Rather, organizations need to include it as part of a data culture with all of the products working in union.

To deploy Power BI successfully, do not use it to simply design reports. Instead, design a culture and architecture. This is one that allows business users to understand, interpret and react to rich and powerful data driven insights.

The many additional products, services and capabilities that come packaged in Power BI are too frequently overlooked. As a result, people see only the top level – visuals in reports and dashboards. But there is a whole host of rich and exciting features below the surface.

With that, here are some common mistakes I have frequently seen new users make when rolling out Power BI.

Mistakes made to under utilize Power BI
- Using it for Data extraction
  Large tables with a selection of filters that you may or may not look to export. Instead, Power BI is designed for trends, insights and cross slice and dice. Large tables and data dumps do not give insight.
- Using it for a data visualization to tell a single point
  Design a visual that can convey information quickly, rather than an infographic type solution. If you are looking for that pixel perfect data visualization for a news story that tells a specific point, there may be other options. Paginated reports or predesigned Excel documents are viable options. Design data pipelines that are regularly updated. Create visuals that are designed to be interactive. This will help users drill down and find insights.
- Ad hoc only reporting
  While this can be a great tool for ad hoc reports, you may be underutilizing and doing extra work. Instead, build reusable data models that are designed for multiple reports. Write DAX business logic and KPI that can serve as a single source of truth. Be sure to document your measures inside the data models. By clearly documenting measures data consumers will understand how to use the data model to build new reports.
- Current reporting tool / Excel replacement
  A common request is to “lift and shift” all excel reporting into Power BI. These products are different and have different uses. If you are moving to Power BI, don’t try and recreate old solutions. Instead, a better approach is to design new reports that play to Power BI’s strengths. Utilize the rich features and powerful engines that make Power BI beneficial. This is a story of it’s better together. Using just Power BI or just Excel has it’s advantages and dis-advantages. Conversely, using both Power BI and Excel can play to each tool’s strength.
- Not building a data culture
  Matthew Roche has an amazing blog series on building a data culture with why and how to do this. Building a good data culture is vital for adoption within the organization. The data culture will start with an Executive sponsor who can push for adoption. So, first and foremost, be sure to have a leader who believes in your vision.
Mistakes made when deploying Power BI solutions
- Focusing on raw numbers, not business insights
  Instead of simply displaying numbers, great reports often have the following KPI, trends, drill down, interactivity and slicing capabilities. This allows business users to gain meaning information about the direction for the business.
- Ignoring the deployment approaches
  Many business users are familiar with a typical process for reports; a user submits a ticket to IT. IT writes a bunch of SQL queries to get the data for this request. They then surface the data in tables and simple graphs. In contrast, Power BI does a great job at breaking down this long turnaround and getting the data in users hands quick. An organization should deploy a top-down, blended or bottom-up approach. As a result of utilizing this approach, they can merge the business and IT side of operations and remove silos.
- Failing to Think like the Business and Act Like I.T.
  The I.T. organization has many strengths related to how to make data available quick and reliably. Power BI is mainly designed for business users. Thus, Power BI has features that borrow from best practices from I.T. One such best practice is the use of Deployment Pipelines.
- Not utilizing Data Models or ignoring self-service reporting
  Data models, as described in this blog by Matt Allington, contain all the metadata needed for reporting. This includes the business logic and data transformations. However, creating and maintaining these can be time consuming. Instead, it is possible to reuse data models and keep one source of the truth for many reports. The modeling experts can own and maintain the models. Furthermore, business users can connect and build their own Power BI reports utilizing the models. This is done without even needing to write a single line of code.
- Treating Power BI as a stand alone product, not part of the greater data or AI solution
  You should not treat Power BI should as just a visualization tool (read this blog by Gil Raviv). Instead, Power BI is a business insights tool, a way to serve and communicate the information within the organization. In addition ML and predictive analytics are baked into it, as are ETL processes, data storage and security. As a result a unified approach to a data culture should be built. Users from all business areas need to be aware of the strategy.
Using Power BI the right way

Power BI should be unified and part of the entire data stage – not a visualization layer on top of it. A modern data platform typically has 4 steps:
- Load and Ingest – extract the data out of the source system and transform it.
- Store – Land this data somewhere so we can run analysis on it.
- Process (or transform) – Run analytics on your data and draw out KPIs, AI and predictions.
- Serve – present this data in an easily way for stakeholders to consume it.
Power BI can be all of these steps. From a single report using power query (Load and Ingest) to import data (Store). Next, you can build a model and DAX measures (Process). Lastly, you can surface the data in visuals on the report pages (Serve).

This can be a more enterprise level solution and scale well too. Firstly, Dataflows are set to extract and transform data from many sources (Load and Ingest). You can back-up and store in a data lake gen 2 storage (Store). Secondly, the data can take advantage of automated ML (AutoML) and cognitive services. Build DAX expression over them, combining a powerful DAX language with the power of AI (Process). Last, you can package these as reports, dashboards, apps or embedded into other applications (Serve).

Alternatively, Power BI doesn’t have to be all these steps. A traditional data platform architecture is described by Microsoft in the picture below. You can utilize other tools such as Data Factory to Load and Ingest data. Next, you can use Databricks to Process/Transform the data. Power BI and Analysis services models will serve the data to the end user.
This is a great example of Power BI fitting into a greater data solution. However, you should implement the deployment with the entire solution in mind. Power BI is not as a tool for simply creating visuals. A good deployment is deeply rooted in the culture. Each step must consider the others in the pipeline, not sit in silos.

Source: Microsoft

Bonus: See this great diagram by Melissa Coates, showing Power BI end to end features.

Azure Synapse

Microsoft is expanding this ecosystem with Azure Synapse. As they roll it out, they are designing data engineering as a single platform. This combines this entire pipeline and tools into a unified experience. Power BI being a part of this platform.

Source: Microsoft

Synapse provides Consistent Security

When we think about user level security, Azure Active Directory (AAD) is the gold standard for access and security for organizations. Synapse leverages this technology to remove friction between different azure components. You can leverage AAD across the multiple services for data factory, Data Lakes, SQL and Spark compute as well as Power BI.
The experience of governing data on a user by user basis improves with the Synapse experience.

A Low Code Data Engineering Solution

There are many Azure components you can use to produce a well engineered data pipeline. Azure Synapse brings all these tools under the same portal experience. For example, using Azure Data Factory, then writing data into a data lake. Picking up the data and querying flat files with compute engines such as SQL or Spark. Azure Data Factory also has built in features that can simplify data lake creation and management using mapping dataflows.

More Computing Options

No longer do We have to choose just SQL or Spark, rather We have options. We can use Provisioned SQL which was previously Azure Data Warehouse. Synapse now offers on-demand SQL, and Spark compute engines. This is where we are really seeing the technology move to where we have separated the storage layer from the compute layer. This means Azure Data Lake Gen2 serves as storage, and SQL and Spark serve as compute.

One Place for all information

Whether it is Azure Data Factory, Spark, SQL or Power BI. Synapse has now become the single portal for integrating all these services. This in general simplifies the experience and management of all your data pipelines.

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
October 23, 2020
Hot Swap Report Connections – External Tools
Latest Version Download:

Download the latest version of Hot Swap Connections using Business Ops

Hot Swap Connections

Splitting models from reports has great advantages, but can make it harder to edit. When editing a model it is useful to see how you it will effect the reports. Using live connections would mean republishing the model back to the cloud and then refreshing the connection for every change you wish to test. In addition, you would probably want to make test workspaces to not overwrite a live production model while developing.

Now there is an external tool that can help solve these issues. The tool has two functions. The first will allow you to switch from a live connection to directly connecting to an open Power BI report. This will allow “Local Development” so that it can be done on your machine without needing to republish. Changes can be seen instantly and time spent on testing can be dramatically decreased.
The second will removed any connections to allow to reconnect to a shared dataset or AAS model.

Instructions to Install

Please install using the official installer here:
https://powerbi.tips/2020/08/one-tool-to-install-them-all/

Using the Hot Swap Connections Tool

After installing the tool, click external tools the Hot Swap Connections to launch.

Connect Tab

This tool will remove any live connections from the selected report and connect it directly to the Power BI report it was launched from. This will only remove live connections so you cannot accidentally delete entire models.

You can choose between Overwrite and connect or Copy and connect. Selecting Overwrite will directly edit that file by removing the connections and replacing with a live connection to the current file. Selecting copy will leave your file intact and create a copy in the same directory with the suffix defined in the settings tab.
It will then open the report that is connected to the model file.

Steps:
- Open your Model file
- Select the Connect tab
- Run Hot Swap Connections
- Choose to Overwrite or Copy
- Select Report file to connect
Remove Tab

This tool will remove any live connections from the selected report and open the file. This is useful when you have made local edits and want to connect it back to a dataset or analysis services model. This will only remove live connections so you cannot accidentally delete entire models.

You can choose between Overwrite and remove live connections or Copy and remove live connections. Selecting Overwrite will directly edit that file by removing the connections. Selecting copy will leave your file intact and create a copy in the same directory with the suffix defined in the settings tab.
It will then open the report that has no connections.

Steps:
- Open any Power BI report
- Select the Remove tab
- Run Remove Connections
- Choose to Overwrite or Copy
- Select Report file to remove connections
The script will leave all visualizations and report features intact. But, all connections will be removed. When you open the report again in power bi desktop, all visuals will appear broken:

This is because you have removed all data from the report. Select a new data source to connect the report to. If the new source matches the names of the columns and measures used in the visuals, they will all repopulate.

Settings Tab

When selecting Copy and connect or Copy and remove live connections, the tool will create a copy of your report first so you do not directly edit you report file. It will place the copy in the same directory as the original and add a suffix as defined in the settings tab.

Watch the webinar below

Steve and Mike talk through the external tool and see it in action!
August 13, 2020
Analyze in Excel – the Advanced Method
A common ask from users is the ability to view data related to visuals in excel. While there is an option to export data, the format is often unfriendly and has limitations. What’s more, this data becomes disconnected and is not updated if a user looks at after the data is refreshed.

This post will explore a way to add a pre-built excel report that can be designed in advance with company branding and advanced features or VBA. Additionally, it will be linked to the report’s data model via live connect. This means it will continue updating if reopened, as well as maintaining Row Level Security.

End Result

The end result will be a download button that can open a well designed excel report.

Video

This content was demonstrated at the Milwaukee Brew City User Group in February 2020. You can watch the video recording and see the steps detailed below.

Step 1 – Connect to Excel

There are two ways to connect:

Option 1:
This option may not be available to you depending on your office license.

Open Excel and navigate to the data tab. Click Get Data the select “From Power BI dataset”.
From the list, select the report that contains the model.

Option 2:

The other way is to navigate to the service (app.powerbi.com) and open the report that contains the model. Click the ellipsis and select Analyze in Excel.

This will download a file which has the extension “ODC”, which stands for Office Data Connection. This file contains information that will tell excel how to connect to the dataset on the service.

For more information, see:
https://docs.microsoft.com/en-us/sql/reporting-services/report-data/use-an-office-data-connection-odc-with-reports?view=sql-server-ver15

Click on the file to open in excel. Once opened, Excel will present you with an empty PivotTable. If you check the PivotTable fields you will notice that you can now see the fields and measures. The same ones which were created in your Power BI model.

We have now live connected to the model we have published in Power BI service. This works the same as using a live connection in Power BI. Your Excel file has access to all the logic and data is updated as soon as the model is updated. Refreshing the data source in Excel pulls the latest data directly from the Power BI model.

Step 2 – Develop a report

The PivotTable in the report is an OLAP (Online Analytical Processing) PivotTable and has a few differences from standard PivotTable made from flat data.

Just like Power BI, dimensions from multiple tables can be used in the same PivotTable. The relationships set up in the model will be observed.

Only explicit measures may be used in the VALUES section of the PivotTable. This means that unlike Power BI, you cannot drag a numeric column into VALUES and select a summarization (also known as an implicit measure). All values must have a specific measure written in the model file (or use an extension OLAP pivot table tools).

The PivotTable sends a new query each time some element in Excel is changed. For example, if you apply a filter, Excel will send a new query to the model. This works similar to the Direct Query mode in Power BI.

Now we have a connection set up, you can make a pre-defined report that you can give directly to your end users.

You can add a slicer by clicking on the PivotTable, click on the PivotTable Analyze tab and select Insert Slicer (or use Timeline for dates).

There are many advantages of using a live connection.
- The Excel file can be branded with company logos
- Pre set filters can be added.
- More advanced features such as Data Solver, VBA and macros can be added.
In addition, the data can be updated inside the workbook, or set to be refreshed on open. Users will also be prompted to sign in and authenticated through their Office account. Features such as Row Level Security will continue to work.

Keep it FRESH

You can set the report to refresh every time it is opened. Navigate to the Data tab and click “Queries and Connections”.

There will be one query, usually starting with pbiazue://. Right click this query and select properties.

From here you can enable “Refresh data when opening the file”.

Step 3 – Upload the excel

Now, that the excel is created, you can distribute it to end users. A helpful way is to create a download link directly in the report.

A good place to host this would be SharePoint – you can link a SharePoint site directly to the Power BI workspace. It is secured through Active Directory, plus if users are already logged into Power BI they do not need to sign in again.

First, navigate to the online SharePoint location where you wish to host and upload your excel file.

Next, right click the file, and select download. This will download the file back to your local machine.

Now, navigate to your browser’s Downloads page. You can access through the settings of the browser, or if you are on a Windows machine, most browsers you can use the shortcut Ctrl-J. From here you will see the file you just downloaded. Right click the link and select what resembles “Copy link”. This may vary depending on the browser:

Chrome:

Edge:

Firefox:

Save the link you have copied to be used later.

Step 4 – Adding to the Report

For my download button, I am just using a down arrow image which I downloaded from an open license image site

In Power BI Desktop, open the Insert tab and select Image. Select the image you wish to insert.

After it has been added, select the image and open the visualizations pane.
Turn on Action and change type to web URL.
Then paste in the URL (the direct download from SharePoint) you saved earlier.

To tidy it up, I will add it next to the related visual. My visual has a text box above it. I have left some white space for a download button.

Step 5 – Using the button

Now, users can navigate to the report and click the download button. This will download the pre-built excel file. Users will be required to sign in and Row Level Security will still apply. In addition, users can use the “Refresh” button in the data tab to refresh data directly from the connected report.

Due to this being a Live Connection users will be required to enable content. In addition, they will need to sign into excel so that they can authenticate to the data model.

Tips and Tricks
- Large tables will still need to send DAX queries for each value, so make sure the DAX is efficient.
- If using CUBE formulas, these are sent as individual queries to the model. Try to use a single pivot table rather than multiple formulas where possible.
- Slicers are useful, but can slow down reports, especially if you have many. One way you could speed it up is by right clicking on the slicer and going to slicer settings. Then untick “Visually indicate items with no data”. This will now always show all items in the slicer – even if there is no data associated with it. While this is less helpful, it can often dramatically speed up reports.’
- Download OLAP PivotTable Extensions (https://olappivottableextensions.github.io). This is an amazing extension that can help see information about OLAP PivotTables. The functions are detailed on the website, but two features which are worth calling out:
  - Ability to see MDX query – this shows the query that is sent to the cube for the PivotTable
  - Ability to add calculations – you can add measures (written in MDX) to the specific PivotTable without having to add it to the cube
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat
July 22, 2020
Split an existing Power BI file into a model and report
Shared datasets are a great way to share data models across the organization. This enables users to maintain one source of the truth and increase efficiency. However, models should be built separate to reports. Meaning it can be difficult to transform an existing report into a shared dataset.

This post describes how to split a pbix file with report and model, into two separate files One for the model and one for the report. This is accomplished using a PowerShell Script. By using this technique any report can quickly be split into a data model file and live connection report file.

Note: that this script is not officially supported by Microsoft. This code is provided as is without any guarantees. The code will alter the internal files, so please keep a backup if you are unsure of anything.

Think like the Business, Act like I.T

Being a great Power BI developer can often mean more than just building visually impressive reports. Focus should be given to efficiency, reusing design and data modelling where possible. In addition, there should be “one source of the truth” – different reports should not have different methods to calculate the same KPI.

This is accomplished by the creation of Shared Datasets. Users can publish a report that contains no visuals, only a data model. Multiple reports can then be built off this model by using a live connection and use the same data model. The model contains global measures. A Global measure is written and stored in the model file. Then the Global measures are re-used by other reports via the Live Connection. This ensures all reports have the same data model, logic and refresh status.

There are many reasons you should consider this approach, which is out of scope for this article. If you are less familiar with shared datasets, I encourage you to visit the following resources:

Think like the Business, Act like I.T user group – global models

Power BI datasets: A Method to the Madness article

Planning A Power BI Enterprise Deployment whitepaper DOWNLOAD
( Chris Webb and Melissa Coates) – section 9

Use Cases

Shared datasets are great, but what if you already have a file with a model and report in one? Currently in order to split a file this would need to be manual, by either copying all the visuals over to a new report (you would also need to re-record any bookmarks, filters, layouts etc. ) or to copy all the Power Query queries (you would then need to set up relationships and re write all measures). This can be time consuming, especially on a large report or model.

Luckily for you, this code will do all the hard work for you. Simply run the code and select a PBIX file. It will create two new files, a report and a model.

Running the code

Right click and select the option Run with PowerShell in the menu.

A menu will open up. Select the power bi file that you wish to split.

Click the button OPEN to allow the script to modify your file.

The script will then create two copies of the file and add the suffix _model and _report. Feel free to rename these, if you desire.

Publishing to the Service

Model File

Open the _model file. It’s a good idea to add some text to the report explaining this is only a model file, so others understand the purpose. Example:

Publish this file to the desired workspace.

Report File

Open the _report file. The script will leave all visualizations and report features intact, but all connections will be removed. When you open the report in power bi desktop, all visuals will appear broken:

If prompted, make sure you discard changes – this will completely detach the report from the source.

Click Get Data and Select Power BI Datasets.

In the pop up window, select the model report you published in the previous step. This will now restore all visuals to display again.

Publish the report to the desired location.

That’s it! you can now share your new model file and continue to build reports off it.

Download the Script

You can download the script described below

Download the PowerShell file and save it to your local machine.

Script
```
#This script was designed by Steve Campbell and provided by PowerBI.tips
#BE WARNED this will alter Power BI files so please make sure you know what you are doing, and always back up your files!
#This is not supported by Microsoft and changes to future file structures could cause this code to break

#--------------- Released 6/2/2020 ---------------
#--- By Steve Campbell provided by PowerBI.tips ---


#Choose pbix funtion
Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "PBIX (*.pbix)| *.pbix"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}


#Error check function
function IsFileLocked([string]$filePath){
    Rename-Item $filePath $filePath -ErrorVariable errs -ErrorAction SilentlyContinue
    return ($errs.Count -ne 0)
}


#Function to Modify files
Function Modify-PBIX([string]$inputpath, [string[]]$filestoremove){

    #Make temp folder
    $temppth = $env:TEMP  + "\PBI TEMP"
    If(!(test-path $temppth))
    {New-Item -ItemType Directory -Force -Path $temppth}

    #Unpackage pbix
    $zipfile = ($inputpath).Substring(0,($inputpath).Length-4) + "zip"
    Rename-Item -Path $inputpath -NewName  $zipfile
              
    #Initialise object
    $ShellApp = New-Object -COM 'Shell.Application'
    $InputZipFile = $ShellApp.NameSpace( $zipfile )

    #Move files to temp
    foreach ($fn in $filestoremove){ 
       $InputZipFile.Items() | ? {  ($_.Name -eq $fn) }  | % {
       $ShellApp.NameSpace($temppth).MoveHere($_)   }  
    }
    
    #Delete temp
    Remove-Item ($temppth) -Recurse
    
    #Repackage 
    Rename-Item -Path $zipfile -NewName $inputpath  
}




#Choose file
try {$pathn = Get-FileName}
catch { "Incompatible File" }


#Check for errors
If([string]::IsNullOrEmpty($pathn )){            
    exit } 

elseif ( IsFileLocked($pathn) ){
    exit } 

#Run Script
else{    

    #set variables
    $modelfiles   = @( 'SecurityBindings', 'Report')
    $reportfiles   = @('Connections','DataModel',  'SecurityBindings')
    
    #Copy files
    $pathf = Get-ChildItem $pathn
    $reportname = [io.path]::GetFileNameWithoutExtension($pathn)
    $model = ($pathf).toString().Replace('.pbix', '_model.pbix')
    $report = ($pathf).toString().Replace('.pbix', '_report.pbix')    
    Copy-Item $pathn -Destination $model
    Copy-Item $pathn -Destination $report

    #modify files
    Modify-PBIX $model $modelfiles
    Modify-PBIX $report $reportfiles
    
}
```
Script Usage License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat
June 8, 2020
Hot Swap Power BI Report Connections
Let’s say you have a Power BI file connected to an Analysis Services machine. Then you want to change the data source to PowerBI.com using a Live Connection. Well, you are out of luck. Until NOW!!

This post describes how to remove a connection from a report using a PowerShell Script. This means any source you can just delete the data source and then re-point your report.

Note: that this script is not officially supported by Microsoft. This code is provided as is without any guarantees. The code will alter the internal files, so please keep a backup if you are unsure of anything.

Use Cases

Image the following diagram. Here we have a Power BI Report Connected to an analysis services model. Now, you wish to re-point the model to a Power BI dataset.

Note: we assume the analysis services model and the Power BI dataset model have the same definition. Meaning all the columns and measures are the same.

The below script removes connections Power BI Report.

Download Script

Download the Power Shell Script here

Note: Running it on a file without a live connection will not have an effect on the file.

Running the code

Download the PowerShell file and save it to your local machine.

Right click and select the option Run with PowerShell in the menu.

A menu will open up. Select the power bi file that you wish to remove the connection from.

Click the button OPEN to allow the script to modify your file.

The script will leave all visualizations and report features intact. But, all connections will be removed. When you open the report again in power bi desktop, all visuals will appear broken:

This is because you have removed all data from the report. Select a new data source to connect the report to. If the new source matches the names of the columns and measures used in the visuals, they will all repopulate.

How the Code Works

Internally, PBIX files contain a selection of metadata and media files. The files are zipped and packaged into a pbix file.

The PowerShell script will alter these internal files, removing the Connections file which holds the information to the live connection.

It then removes a security file, SecurityBindings, that is necessary to remove in order to not corrupt the file. SecurityBindings will be repopulated the next time you edit and save the file.

Script
```
#This script was designed by Steve Campbell and provided by PowerBI.tips
#BE WARNED this will alter Power BI files so please make sure you know what you are doing, and always back up your files!
#This is not supported by Microsoft and changes to future file structures could cause this code to break

#--------------- Released 5/28/2020 ---------------
#--- By Steve Campbell provided by PowerBI.tips ---


#Choose pbix funtion
Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "PBIX (*.pbix)| *.pbix"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}
#Error check function
function IsFileLocked([string]$filePath){
    Rename-Item $filePath $filePath -ErrorVariable errs -ErrorAction SilentlyContinue
    return ($errs.Count -ne 0)
}


#Choose file
try {$pathn = Get-FileName}
catch { "Incompatible File" }


#Check for errors
If([string]::IsNullOrEmpty($pathn )){            
    exit } 

elseif ( IsFileLocked($pathn) ){
    exit } 

#Run Script
else{    
   
    #Unzip pbix
    [Reflection.Assembly]::LoadWithPartialName('System.IO.Compression')
    $zipfile = $pathn.Substring(0,$pathn.Length-4) + "zip"
    Rename-Item -Path $pathn -NewName  $zipfile

    #Delete files
    $files   = 'Connections', 'SecurityBindings'
    $stream = New-Object IO.FileStream($zipfile, [IO.FileMode]::Open)
    $mode   = [IO.Compression.ZipArchiveMode]::Update
    $zip    = New-Object IO.Compression.ZipArchive($stream, $mode)
    ($zip.Entries | ? { $files -contains $_.Name }) | % { $_.Delete() }

    #Close zip
    $zip.Dispose()
    $stream.Close()
    $stream.Dispose()

    #Repackage and open
    Rename-Item -Path $zipfile -NewName $pathn 
    Invoke-Item $pathn 
}
```
Editing the Code

The code runs on PowerShell. PowerShell is a cross-platform task automation and configuration management framework, consisting of a command-line shell and scripting language.

If you want to view or edit the code, first open PowerShell ISE. We recommended to run as administrator. Afterwards, you can paste the code below into the editor.

Other Considerations

The script can be useful when using two power bi files for development and deployment to the PowerBI.com service. In this use case you will have one Power BI that is the bifurcated model and one report that has just report pages. To speed up development you can Open both the Model file and the Report file. The Report file can point to a LocalHost version of the running Analysis Services.

Here is an example of what this architecture would look like.

Script Usage License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat
May 29, 2020