In the October 2020 release of Power BI desktop you have the ability to load a dataset from the splash page. For this tutorial we dig in on how Microsoft enables a default dataset. Additionally we show you how to customize the default dataset for your needs.
Quite often I need to prototype a visual, or work on some sample data to design a report. The very first step is always loading some sample data. Now that Power BI desktop comes with a default dataset, we leverage this feature to speed up our development process.
Watch the YouTube Video
Additional Thoughts
In this video I explain that the dataset does not auto load with datatypes enabled. This was due to my default setting within Power BI desktop. If you’d like you can make Power BI Desktop auto detect your datatypes for you.
This setting can be changed by the following steps:
Click on the File button
In the drop down menu, Click on the Options and Settings
In the menu on the right Click the button labeled Options
Under the Global section in the Options menu Select the item labeled Data Load
Change the Type Detection for loading data
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:
This month I had the privilege of participating in the Power BI Quiz. If you don’t know about the Power BI Quiz show, this is an event that is put on by Just Blindbaek. Just does a great job of creating a Season of Quizzes. Experts from across the Power BI community are co-hosts and submit questions about a specific topic.
This week the topic was all about themes. Thus, I whipped up some questions and had an absolute blast!
Thank you to everyone who was able to participate and play the game.
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:
We were excited to welcome a good friend of PowerBI.Tips Alex Powers to speak with us at this months PUG meeting. Alex blew a bunch of minds by diving into the depths of Calculated Columns and the love or hate relationship we should have with them.
Calculated Columns are a common element to the new users. Those users typically come from a default analyst mindset of “see the data” then perform the calculation. This pattern can lead to a bunch of performance issues. Alex shows us how to be intentional with our decision making and provides some solid advice throughout. Be sure to check out this video as it is packed with great insights and conversation around this topic for the beginner as well as the long time expert.
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
Like and Follow
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, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
This month we got the Power BI desktop update a little bit later because it was released during the 2020 Ignite conference. As always the MVPs are super excited to talk about the latest Power BI features.
This month’s release was packed with all sorts of new features. For a full list of features read the official Microsoft Blog post. Some of our favorite features that the MVPs talked about are:
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:
What does it take to design and create a great looking report in Power BI? Do you have a clear understanding of what tools and techniques you should use every time? Recently the owners of PowerBI.Tips had the pleasure of speaking with Reza Rad’s user group on the fundamentals we use to build great looking reports every day.
This presentation focuses on the basic concepts and things we do for every report. This one isn’t about all the unique features we love talking about leveraging, but rather the nuts and bolts that you can use to make every report shine.
Key Concepts
We’d invite you to join us by watching the video where we cover these fundementals in depth.
Discover insights. Who is your audience and what questions can you ask to drive into those key metrics
Define and outline the process for developing key visuals based on those insights
How can you create and use a background to enhance the look & feel of your report
Creating a consistent theme that you can re-use again and again
Using tools like Themes, Scrims or Layouts to help you make this experience more achievable
Designing a Great Power BI Report
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:
This tip outlines an easy deployment method for data tables that have been manually added to a model via “Enter data” in Power BI Desktop. This is a very effective method especially as it relates to large model deployments. The scenario is typically seen when you build data tables to support slicer/measure interactions. As those selections are made, each one of the values will generate a different DAX calculation to drive a different insight.
An example of that setup would look like this.
Create a data table
First, manually create a data table for slicer selections.
Create Measure and Slicer
Create a Measure that uses separate calculations ,and reference the data table in our slicer to determine which measure we want to display.
As seen above, the slicer has the value from the manually entered data. The id’s correspond to the measures you will want to display in the report visual.
This pattern gives an amazing amount of flexibility to use the same visual and look at different metrics within the same visual. This removes the need for bookmarks and more visuals for each calculation.
Typically, I deploy all my metadata changes via ALM toolkit. (I love that tool). However the slicer options and calculations in this method have a data element that needs to be refreshed as well. Which suggests I need to refresh the data model with these types of updates. However, I don’t want to refresh the entire model. So, I opened up my connection to my model in the Power BI Service to investigate.
You can connect to your Power BI workspace Analysis Services models by following these steps.
Connect to Analysis Services Model in Power BI
First ensure you have the XMLA endpoint enabled appropriately. This is under Settings > Admin Portal > Capacity Settings > (Select Capacity) > More Options > Workloads
Once we have the capacity setup up. Copy the link from your Premium Workspace. Click on the ellipses next to the workspace and select Workspace settings.
Under Settings Copy the Workspace Connection
Open SQL Server Management Studio and apply the connection string in the Server name: location. (Remove the ending of “Initial Catalog” if it is present initially. Set the Authentication to AAD-Universal with MFA and enter your user name (email address) in that section.
Locate the Code
After getting hooked up, my “There has got to be a way” hat went on and I started digging into the objects in SQL Server Management Studio (SSMS).
Mike and I noticed that the entire Power Query query is part of the connection within the table definition. Part of that query is the binary string used when a manual data table is created. Could it be, we could update the local PBIX, grab that binary code and replace the binary in this table!? “YES!”
Here is how you do that.
Right click on the table name > select “Script Table as” > select “CREATE OR REPLACE to” > select “New Query Editor Window” (SSMS hates screenshots apparently, so I could not snag them)
Now that you have the query open, scroll down till you see your Power Query code. Search for the very begining where you see the binary for your data table. Replace this binary string with the new string you have in your PBIX file.
Pay close attention that the binary is wrapped in ‘\’ in the front and before the double quotes at the end. You have to keep it that way or it will break your code.
Execute the query
Refresh your Power BI dataset table
Right click and select Process Table to refresh the data in the table. Choose Process Data
BAM! You’ve just updated the table in the Service without the need to refresh the entire model!
As a result of these findings, I’m very interested in what other parts of the Power Query connections we can update/modify. I think I’ll be exploring those further in the coming months. I am really excited that Power BI has moved onto the Tabular Object Model and we now have the ability to use XMLA read/write. There is so much flexibility and speed in our deployments now! We’re looking forward to finding new and interesting ways to push changes without the data. We hope you find this tip useful, thanks for reading.
Requirements for the above tutorial to work:
Be using a Premium Capacity
In your Power BI Desktop, enable the enhanced metadata format
Toggle on the XMLA Read/Write in the Power BI Service (described above)
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, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
Back again with another Power BI User group for Milwaukee. This month Mike and Seth walk through the beta release of external tools. If you didn’t see the announcement from Microsoft but in July of 2020 was the release of External tools.
What are External Tools? I’m glad you asked. An External Tool is an application that can be used along side with Power BI. Parameters can be passed from Power BI desktop into the External Tool, such as a connection string to the data model.
We are super thrilled about this new ability to enhance the Power BI desktop experience so much that we built a custom installer that will enable users install many of these external tools all in one place. This is why we build Business Ops.
Watch this month’s PUG to see how to install External Tools for your machine.
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:
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.
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!
We are excited to announce a new tool from PowerBI.tips The Business Ops tool. To get this tool Download it here.
For the Full Story Read on..
There are many exciting features that we love to see and share when it comes to monthly Power BI releases! The continual improvements still inspire and provide a deeper and richer experience for us all to WOW our report consumers. There are some periodic major changes that come, and we think one of those has arrived with the latest announcement of 3rd party tool integration.
Add PowerBI.tips Front End tools
After the great news above, we had our heads down looking at the documentation and were very interested in how we could add our own tools to the External Tools section. After some digging, and reviewing a fantastic blog by Eric Svensen we locked in on how we could our tools even more accessible for you!
We want to help you get to your favorite PowerBI.tips tools quickly and easily. What follows is what each of these tools offers, and how to easily connect one or all into your Power BI Desktop files for quick connection and use.
I don’t see “External Tools”
The first requirement to see the External Tools section is that you must be on July 2020 Desktop. You will NOT see the External Tools section right after you open it. The reason for that is varied, but suffice to say, you need a specific JSON folder created in your Power BI folder structure and an executable to run. We’ve made this easy for you, so just read through our choices of tools and we’ll walk you through how to get these buttons created and working for you in no time!
Tool Choices
PowerBI.tips – Part of staying engaged is staying current. What better way to do that then start everyday with a link to your favorite site – Power BI.Tips
Themes – Need to take the general settings you can do in the Desktop to the next level? Adding our Themes capability into your reports gives you full control over the customization of color, font and properties.
Charts – Utilizing the amazing work from MSFT research in Charticulator, we’ve created a version called Charts. This tool lets you build no code custom visuals that work right alongside the out of the box one’s. Check out our videos if this is new to you!
Themes Gallery – Sometimes you just need to find a little inspiration. Look no further than our extensive gallery of color themes. Easily pick and download any color theme you find.
Themes Gallery
Charts Gallery – Learning how to build the custom visuals in Charts takes a little bit of time, but while you are learning that craft, or are looking for a unique visual check out the gallery of already built custom visuals you can integrate into your reports.
ChartsGallery
Layouts – Need to take your report look and feel to the next level? Our Layouts provide some of the best looking backgrounds tailored specifically to Power BI. We use pop up dialogues for contextual explanation, page navigation and hidden slicer functionality to amaze your end users.
Scrims – Backgrounds for Power BI. We take all the principles we’ve learned in visual design to create amazing visual canvases that don’t distract from the data. Using these single images reduces the need to render all the different shapes and objects on your page making it a faster/better option.
Model – Are you a data focused individual? Have you played with Power BI dataflows? Use our Model tool to create the metadata objects you want to utilize in your Common Data Model.
Connections – Streamline your report files for other report authors by pre-configuring the data source connection. Not familiar with how you create PBIDS files? Check out our tutorial.
We know our favorites, but you probably have yours as well. Throw in a dash of IT security and you might not have the best experience with a chosen browser. As we know that is the case, you can choose one of our 3 options for kicking off these tools from the Desktop and opening the right browser.
Install Your Favorites!
Featured External tool tutorials
Want to know more about the featured tools in the MSFT blog? We had the chance to welcome the creators of those tools to tell us all about them. Be sure to check out the webinars for all the details on how to get started with them and all the capabilities they offer.
ALM Toolkit – Schema compare and deployment tool. Fantastic for Application Lifecycle Management (ALM). How to use ALM Toolkit.
Tabular Editor – An amazing modeling tool that makes working with your model a breeze. 4 part series.
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:
The Monkey Tools add-in for excel is really amazing. As a developer for Power BI for many years I’ve learn a ton of tips and tricks while working with Power Query. Watching Ken demo the high level of automation that Monkey Tools really blew my mind. How did I not know about this tool earlier?! My development patterns have now forever changed.
This presentation for Monkey Tools is presented by it’s developer MVP Ken Puls. Ken is a staple and expert in the Excel community. Not to mention, he is an incredible presenter. You will enjoy this session from Ken.
What are Monkey Tools?
Monkey tools in short is a simple way to automate data modeling within Excel. There are some incredibly helpful portions such as making date calendars, changing how data is handled during Power Query loading process and many more.
Ken gives us a great overview and starts digging into the amazing features of this tool in this two part series about this tool.
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:
