Select Page

Retrieving Dynamics 365 OptionSet labels in Power BI

Recently I was working on developing a range of Power BI reports based on data from Dynamics 365. There were are few times when I needed to display OptionSet labels in the reports, and these labels were not available directly from within the entity. I had OptionSet codes but no labels. There are a few blog posts about this including one that requires installing a plugin, however it is relatively easy to retrieve these from the API with a few steps.

This answer on stackoverflow was helpful however it required passing the MetadataID of related entity rather than the LogicaName. To make my life easier I developed a Power BI function that would take the name of the entity and the name of the optionset and then perform two requests – the first to retrieve the related entity metadataID from the name, and then the second to retrieve the optionset labels using this ID.

(EntityName as text, OptionSetName as text, DynamicCRMAPI as text)=>
let
 Source = OData.Feed(DynamicCRMAPI & "/EntityDefinitions(LogicalName='" & EntityName & "')"),
 MetadataId = Source[MetadataId],
 OptionSetSource = OData.Feed(DynamicCRMAPI & "/EntityDefinitions(" & MetadataId & ")/Attributes/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName&$expand=OptionSet,GlobalOptionSet"),

#"Get OptionSet" = OptionSetSource {[LogicalName=OptionSetName]}[OptionSet],
 Options = #"Get OptionSet",
 SelectOptions = Options[Options],

#"Converted to Table" = Table.FromList(SelectOptions, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Value", "Label"}, {"Column1.Value", "Column1.Label"}),
 #"Expanded Column1.Label" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Label",
 {"LocalizedLabels"}, {"Column1.Label.LocalizedLabels"}),
 #"Expanded Column1.Label.LocalizedLabels" = Table.ExpandListColumn(#"Expanded Column1.Label", "Column1.Label.LocalizedLabels"),
 #"Expanded Column1.Label.LocalizedLabels1" = Table.ExpandRecordColumn(#"Expanded Column1.Label.LocalizedLabels", "Column1.Label.LocalizedLabels", {"Label"}, {"Column1.Label.LocalizedLabels.Label"}),
 #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.Label.LocalizedLabels1",{{"Column1.Value", "Code"}, {"Column1.Label.LocalizedLabels.Label", "Label"}})
in
 #"Renamed Columns"

Unfortunately although this worked well in the desktop environment, the Power BI service environment wouldn’t load due the the combination of source requests and the dynamic variable being passed to the second request.

In the end I separated the two functions to load all entities names and IDs and then manually created a query with the entity metadata ID copied in.

Splunk & my Twitter archive

Recently I’ve been having a great time playing with Splunk. Splunk is a big data platform that allows you to search practically any machine data and present it in ways that will give you insight into what you have. It has practical applications for application management, IT operations, security, compliance, big data as well as web and business analytics.

I downloaded the free trial version, installed it locally and played with some personal data sources including phone bills, bank statements, my personal twitter archive as well as some weather data I downloaded from the Bureau of Meteorology.

Below are a few of the interesting charts that came out of my Twitter archive (@dan_cake), along with the basic search query used to extract and present the data in this way. Click to view a full-sized version.

 

Tweets by month

sourcetype=twitter_csv

Tweets peaked in July 2010 when I sent on average almost 4 tweets per day. The first drop in usage is probably due to the birth of my first child and then the subsequent months where there was hardly any usage is due to just being too busy at work and at home.

Tweets per hour of day

sourcetype=twitter_csv | stats count BY date_hour | chart sum(count) By date_hour

Most tweets were sent between 9am-5pm but there is an dip around lunchtime and an interesting smaller increase in usage between 9pm-11pm. What really surprised me about this was the volume of tweets sent between 1am and 5am. Drilling down into the data is seems that some of these are due to issues with the timezone of the device I was on.

Tweets sent by Twitter client

sourcetype=twitter_csv | rex field=client "<*>(?<client>.*)</a>" | eval client=lower(client) | top client

Also I was surprised by this. I know I have been searching for the perfect client but had forgotten just how many I have been through!

The search query involved stripping some HTML tags from some of the client values with regex as well as matching on lowercase to get around inconsistencies with the same client having different capitalisation.

 

Slow localhost on OSX Lion

It seems with the Lion operating system update the addition of support for IPv6 addresses really slows down any localhost resolutions. As usual, a great answer on StackOverflow supplied the solution, capturing the IPv6 lookup as well as the standard IPv4. Thanks to guns for the solution.

Basically you need to add a value of ::1 for every localhost value:

127.0.0.1    mydomain.local
::1    mydomain.local

With regards to overriding domains in the hosts file, I have found that in some circumstances, Lion queries the IPv6 address for a domain if it senses that a domain is unreachable over the IPv4 network.

I discovered this when I noticed some ads that I had never seen before on Snow Leopard because I had redirected the ad domains to 127.0.0.1. I fired up wireshark and noticed AAAA (IPv6 DNS records) queries following the IPv4 A queries (IPv4). The ad servers indeed have IPv6 addesses and were able to serve me their content.

The solution to this is have a “::1” entry for every 127.0.0.1 in your hosts file.

::1 mydomain.com
entry for every

127.0.0.1 mydomain.com
entry in your hosts file.

Interestingly, if you happen to have a local webserver running on 127.0.0.1:80 and your browser receives a response from the webserver (error or otherwise), no AAAA query is issued, as it seems to be satisfied that a TCP connection was at least possible.

Link