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 firstname.lastname@example.org! Also, if you are interested in working with me, please reach out to me at the above email. Thanks for visiting, Katie!
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
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.
Quick Guide video for PBI dashboards
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
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.)
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.
HEX tile Maps for Power BI – something really cool that I haven’t started diving into yet but will soon.
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.
- 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.
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.
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
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/
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/
TIPS AND TRICKS!!
- 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/
The Infographic Designer is also the first custom visual you should import and learn to use!