You've probably seen the cool demos where someone connects PowerBI to CRM and a pretty dashboard is available

I bet at least once you've wondered how they did all of that right? For example, how did they deal with Option Set Values to show a nice label? No more waiting is required Microsoft has made available for download the PBIX file for both content packs and you can download them to either explore and learn or to use them as a starting point for combining with your own custom data. You can download them from here:

Download the Microsoft Dynamics CRM Online Sales Manager .PBIX

Download the Microsoft Dynamics CRM Online Service Manager .PBIX

As you open it up, the first thing you will see is a nice relationship view of all the data they've pulled in.

This doesn't look like the OData names you get if you were to just load up directly from CRM, they've done a bunch of stuff to give nice readable names and even built some measures like Actual Revenue. You can explore and find out how they did all that as you wonder around the model.

If you want to dig in, the best place to start is from the Query Editor - Advanced properties on an entity like Account. This will start showing you the magic they are using to clean up names and map option set values. Here is what the query for Account looks like:

As you look at this you will notice that after the first couple of lines each line builds and refines the prior line. All of the lines that say merge are doing NestedJoins to OptionSet lookup tables (more on that in a minute). Once merge they do an Expand to create a column with the label. Then finally they do a rename columns which is how they put all the pretty column names that you see in the final model.

So how do they do the optionset mapping? It starts by creating a new Blank Query and then in the advanced properties they basically staticly define the optionset value/label pairs - here is an example of what BudgetStatusOptionSet looks like

Once saved, this looks just like any other table in Power BI

To use this in the query of the entity, you add something like the following:

"Merged BudgetStatus" = Table.NestedJoin(#"name of prior table",{"budgetstatus"},BudgetStatusOptionSet,{"Value"},"NewColumn.1",JoinKind.LeftOuter),

For option sets you can also look at a plug-in for XrmToolBox - here https://github.com/MscrmTools/GapConsulting.PowerBIOptionSetAssistant

I would keep going, but you are really better off going and downloading the PBIX files and start exploring!