POWER BI: TIPS, TRICKS, AND INFO
Here is a kind of master list of much of the useful resources and information I've found on my journey to Power BI greatness! You'll find info to help you get started, learn new things, best practice, pulling Microsoft Dynamics CRM and Project Online data, and so much more. Don't forget to check out my blog and portfolio for more Power BI info! If you have any questions or if there are errors on this page, please let me know at kcoriell@gmail.com or in the comments below! Also, if you are interested in working with me, please reach out to me at the above email. Thanks for visiting, Katie!
FYI: As of Nov 2021, there is a ton of updates I need to do with this page and I will hopefully have them completed by the end of the year!
Beginning Power BI Info
Best practice is to build your reports in PBI desktop, then publish to PBI Service (online). And then, when you update your report in Desktop, you can just publish it to the Service (online) again (it will ask you if you want to replace what you already have there – say yes). From PBI Service, you can build dashboards, create workspaces, share with others, etc.
Microsoft Power BI Guided Learning - Great resource but make sure you watch the videos with it too! https://docs.microsoft.com/en-us/power-bi/guided-learning/
RADACAD.COM This is a really great resource. This is just the “beginner PBI” info but I scroll through his posts all the time to get help with issues or find ideas/inspiration when designing PBI reports and dashboards. http://radacad.com/power-bi-desktop-the-first-experience
Guy in a Cube is another great resource for learning to use Power BI, and more advanced skills. https://guyinacube.com
Another good beginner reference – 10 Common Mistakes in PBI and how to Avoid them. https://datachant.com/2017/01/06/10-mistakes-you-always-do-in-powerbi-powerquery/
How to Speak Power BI - learning the software lingo to help avoid miscommunication/misunderstanding. Beginning: http://digital.withum.com/blog/how-to-speak-power-bi-the-basics, and intermediate: http://digital.withum.com/blog/how-to-speak-power-bi-intermediate-vocabulary.
Always create/import a “master” Calendar table into your PBIX, especially if your database doesn’t have a DimDate table. Blog post coming ASAP with options.
Quick Guide video for PBI dashboards
http://groupby.org/conference-session-abstracts/how-to-analyze-sp_blitz-in-power-bi/
Microsoft Power BI Blog is the best place to see what new features are added to PBI on a regular basis. https://powerbi.microsoft.com/en-us/blog/
The PBI community forums are a god-send. Here is the desktop main page, but you can find the PBI online info from here.
https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer and Power BI User Group! https://www.pbiusergroup.com/home
Remember that you will primarily use DAX and PowerPivot(M) as your coding languages in PBI. You can also use R, but I’m not as comfortable or progressed in learning it as I am with DAX/M.
Advanced Power BI Info
Using Bookmarks, Selection pane, Buttons, and personally created “toggles" to create interesting yet easy ways to visualize and organize your data. Will get an embedding PBI report as an example soon. But this is a good blog post on setting up ways that Amanda from Microsoft has shown to incorporate buttons/bookmarks into your design. https://www.wiseowl.co.uk/blog/s2583/bookmarks-visuals.htm
Creating a “formulas” table for all your PBIX measures is my preferred method of setting up my PBIX. Once you hide the blank column, save and close the report, the Formulas table will be at the top of your Fields pane. Blog post with video coming ASAP.
Filtering on a card – I use this a lot. Go down to the Eno1978 response about starting with a table first, then switching to a card. (Aug 2018: Sadly, this trick doesn't work anymore.)
https://community.powerbi.com/t5/Desktop/Problem-with-visual-level-filtering-on-card/td-p/31635
If/Then/Else statements and conditional columns in PBI. This is super useful and PBI makes it fairly easy to set up conditional columns and T/F columns within PBI Desktop for your imported data.
https://www.linkedin.com/pulse/power-bi-else-conditional-columns-madan-bhintade
http://www.avising.com/conditional-code-branching-in-power-bi-query-if-then-else-then-else-if/
HEX tile Maps for Power BI – something really cool that I haven’t started diving into yet but will soon.
http://sqljason.com/2016/04/hex-tile-grid-maps-for-power-bi.html
MS Project Online into PBI – This is great basic info. Here is what I do: 1) I use the specific MS Project Online content Pack for PBI to start, 2) set up loading my data, 3) Go into the Query Editor and then look at what has already set up to be pulled into PBI from Project Online, 4) click on a table on the left menu, you can look at the right side bar – Applied Steps, and double click on [Removed Other Columns] and you can add in more columns from the table that weren’t already set up automatically, 5) Under the ODataUrl table on the left, you can look to see all the potential data tables available to you and then add those to the rest, 6) super important to look at the relationships between your data tables.
https://www.senseiprojectsolutions.com/2016/02/15/power-bi-for-project-online-get-started-quickly/
- MS Dynamics CRM into PBI is very similar to pulling Project Online data, here’s some basic info: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-microsoft-dynamics-crm/
Common query tasks in PBI, also discusses a bit about using the Advanced editor.
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/
Text After/Before Delimiter, extract text, etc. : Go to [Query Editor], then the [Add Column] tab, and then beside (Format) and below (Merge Columns) you’ll see [Extract] – this is super useful!
Setting up DateDiff to calculate number of days between start date and finish date, and if you want to know DateDiff from TODAY (current date), replace Today() for the [EndDateField] in the accepted solutions on this post: https://community.powerbi.com/t5/Desktop/Datediff-start-date-cannot-be-greater-than-the-end-date/td-p/108512
Data alerts in PBI – can set up on PBI Service dashboards. Can use Microsoft Flow to set up to email people when these alerts are triggered.
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-set-data-alerts/
Custom PBI visuals – I’m giving you both the new site and GitHub page – lots of great info on both. Lots of awesome custom visuals. You can also access custom visuals from within Power BI Desktop. First one you should download, import into PBI, and learn to use is the Infographic Designer. FYI: You’ll have to import the custom visuals you want to use for every new PBI desktop report you create.
New - https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals
GitHub - https://github.com/Microsoft/PowerBI-Visuals
Have multiple attribute columns that are the same list of names, descriptions, or titles but want to be able to apply ONE slicer to all of them in your Power BI report? This will help you figure it out - https://community.powerbi.com/t5/Desktop/Once-Slicer-for-multiple-columns/td-p/407949
Dynamically changing shapes in PBI. This touches on building a table with image urls to relate your data to a “state” and have it show as say a red, yellow, or green circle in a PBI table. http://sqlkover.com/dynamically-changing-shapes-in-power-bi-alternatives/
Trailing Twelve Months calculations for Power BI. https://powerpivotpro.com/2018/01/trailing-twelve-months-ttm-power-bi/
Along with TTM link above, what if you want to allow people to dynamically choose how many months (or years, quarters, days, whatever) on your Power BI report page? Using N with this table and measures will help you get this figured out. Thinking it could also provide a way to all people to visualize say Top N Clients with a dedicated slicer on the page. http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-power-bi.html
RADACAD Power BI Helper tool will amazing but I’m still learning how to use it. Download it here: http://radacad.com/power-bi-helper
More on using color graphics in a table/matrix to show data – This is for KPI indicators: https://www.shareadvance.com/powerbi-kpi-indicators-in-tables/
Big question I get often: What is the difference between a Measure and Calculated Column? Here is a basic explanation: Calculated columns are very similar to regular columns that we find in most datasets. The difference is that calculated columns are the result of computations by using two or more columns or using columns from different tables created by us within PBI. They can be used when we want to perform row-wise calculations. Measure, on the other hand, is similar to a calculated column but does NOT occupy any physical memory and the results can’t be seen in the form of a column. We typically use a measure when we want to perform dynamic computations on a group of rows or by grouping data together.
TIPS AND TRICKS!!
Make sure you turn on many of the “Preview” features in Power BI Desktop (and check periodically for new items added here). Go to File \ Options and Settings \ Options \ Preview Features under “Global.” Also remember that you have a lot of options and settings you can set/change once you get to the “Global” and “Report” menus under the Options in PBI Desktop.
In PBI Desktop, go to the [View] tab, and you can manually set how you would like the page to display on mobile devices – then publish to PBI Service, and then you can view mobile version.
Use ctrl c and ctrl v to copy and paste a visualization both on same page and other pages
Use ctrl a to copy and then paste all elements on a page
Unless you manipulate the formatting of your columns in the advanced editor, I tend to not set my columns as whole numbers, currency, percentage, date, etc. in [Query Editor] but wait until I’m in the main area and select the “data table” icon on the left menu, then select the [Modeling] Tab at the top, and then change things.
If you are using percentages in your data tables, and especially if you add a custom column that is a percentage, don’t multiple your formula by 100 to give you a percentage in your data table, leave it as a decimal number. THEN under the left “data table” tab, go to the [Modeling] tab, and then set your column as a percentage – this will then multiply your decimal by 100 and include a % symbol with your data on your report pages.
Merge/Append Tables is a great feature, just spend a little time googling the various differences.
Managing Relationships between your data tables is very important. This needs to be properly set so that you can combine data columns from related tables into one chart/graph/table.
Format painter: click a visual, click format painter, then click on a same type of visualization to switch to former visual’s format
If you want to filter out data, say a column that is true or false and you want to display the count of true items, visualize as a table, filter the visual, then with it selected, click on the card visualization
In the Query Editor, click on the Advanced Editor – I use this a lot for manipulating my data, including getting a table to behave a specific way and being able to copy most of the info in the Advanced Editor to another similar data (takes out a ton of steps if you edit that table in similar way as a previous table).
Remember that much of your data values for PBI report visuals will be a sum/average/median/don’t summarize – you can change these to reflect how you want your data to behave.
Color and Design
Color Codes (PBI uses Hex#) - http://www.december.com/html/spec/colorcodes.html
Help with determining complementary colors, etc. - http://paletton.com
Wanting to figure out a specific color from an image, etc. - http://imagecolorpicker.com/
Advanced Color Theme Generator for PBI reports - https://powerbi.tips/tools/advanced-color-theme-generator/
Create your own clip art to overlay on tables/charts in PBI without having photoshop or the like: http://elearningart.com/blog/create-your-own-clipart/
Use Illustrator or the free Inkscape to create high quality PNG page backgrounds and wallpapers. SVG files are possible, but best practice is to use PNG image files. Example files: SVG and PNG
The Infographic Designer is also the first custom visual you should import and learn to use!