Month: June 2022

Microsoft Technologies, Power BI, Power Query

Generating Unicode Characters in Power Query

Meagan Longoria

You may have used the UNICHAR() function in DAX to return Unicode characters in DAX measures. If you haven’t yet read Chris Webb’s blog post on the topic, I recommend you do. But did you know there is a Power Query function that can return Unicode characters? This can be useful in cases when you want to assign a Unicode character to a categorical value.

In Power Query, you can use Character.FromNumber to return a Unicode character based upon the specified number.

I recently used this function in a Workout Wednesday for Power BI exercise to visualize music notes. I made a scatterplot that used eighth notes and eighth rests as the markers.

To create an eighth note (𝅘𝅥𝅮), I added Character.FromNumber(9834) to my Power Query expression. The eighth rest is Character.FromNumber(119102). There are many music-related Unicode characters available.

With the Character.FromNumber function, we can make a custom column that uses an If statement to determine the correct character to use for each row in a table.

One thing to note is that the query preview in the Power Query Editor doesn’t always render the Unicode values correctly. This doesn’t mean it won’t work in your report, though.

For instance, Unicode character 119102 doesn’t look like the eighth rest when viewed in the Power Query preview.

The question mark in a box represents a unicode character that could not be properly rendered

But after applying changes and sending the data into the dataset, you’ll see your expected output in the Data view in Power BI Desktop.

Output of the custom column containing Unicode characters in the Data view in Power BI Desktop

The Unicode character does show correctly in Power Query Online when creating a dataflow.

While DAX is the answer for generating music notes or emojis based upon aggregated values, Power Query can help you generate a Unicode value per row in a table by using the Character.FromNumber function.

Microsoft Technologies, Power BI

Log in to Power BI Desktop as an External (B2B) User

Meagan Longoria

I noticed Adam Saxton post a tip on the Guy in a Cube YouTube channel about publishing reports from Power BI Desktop for external users. According to Microsoft Docs (as of June 21, 2022), you can’t publish directly from Power BI Desktop to an external tenant. But Adam shows how that is now possible thanks to an update in Azure Active Directory.

To sign into another tenant in Power BI Desktop:

  1. Click the Sign in button.
  2. Enter your email address (i.e., username@domain) and click Continue.
  3. When the sign-in dialog appears, select Use another account.
  4. Select sign-in options.
  5. Select Sign in to an organization
  6. Enter the organization’s domain (e.g., mycompany.org) and select Next.
  7. You will then be shown a list of users with your user signed in to the external tenant. Select that user.

Then you will be signed in with your B2B user to the external tenant. This allows you to build “thin” reports off of shared datasets as well as publish to PowerBI.com from Power BI Desktop!

It would still be better for usability if Power BI had a tenant switcher similar Azure Data Factory or the Azure Portal, but this works perfectly fine in the meantime.

A couple of minor gaps

As Adam notes in his video, sensitivity labels don’t get applied if you publish from Power BI Desktop using an external user.

Also, once you publish, the link in the success dialog won’t work because it doesn’t contain the CTID in the URL. If I click on the link labeled “Open ‘WoW2022 Week 24.pbix’ in Power BI” in the screenshot below, it will try to open the link as if the report were in my tenant.

Dialog in Power BI desktop that indicates that publishing was successful. It says "Success! Open 'WoW2022 Week 24.pbix' in Power BI". The name of the file is a link to PowerBI.com.
Success dialog seen after successfully publishing from Power BI Desktop

That won’t work since the report is in another tenant, so next I’ll get the error “Sorry, we couldn’t find that report”. You can go to the external tenant (by including the CTID in your url) and see your report is published, so don’t worry too much about this.

This tip makes life a bit easier for me when I’m publishing multiple times in a row from Power BI desktop. It’s fewer clicks than using the Upload a file dialog in PowerBI.com. I hope Microsoft will round out the B2B features so all links generated by Power BI have the CTID in the url, and maybe add the tenant switcher, too.