I had a requirement where the customer wanted to make sure that the right user could only see the colour from the stock item assigned to them. Essentially using the Excel file for Row Level Security.

For the Row level security to work correctly it required the colour name to be an exact match.

For me to ensure that this would happen, I wanted to make sure I could get the data from my Power BI dataset, and then using the Excel data validation make sure that the user could either type in the value 100% correct or pick it from the drop-down list.

This is what I am going to demonstrate in this blog post.

In my example below I going to be looking to get the colours from the Stock Item table in my dataset.

I am also going to be connecting to a dataset that is stored in Premium Per User App workspace. I have recently found that when using the PPU App workspace details the actual connection string is a lot easier to read and modify.

Creating the table using a DAX query in Excel

To create the table using a DAX query I am going to use an ODC connection file, which will require some initial details before creating the ODC file.

  • Power BI Dataset Name
    • In my example the dataset name is:
      • 1.WWI Sales – 2020-05-27
  • Power BI App Workspace Name
    • I got the App workspace name from the settings in the Power BI Service
      • powerbi://api.powerbi.com/v1.0/myorg/PPU%20Testing
  • DAX Query
    • EVALUATE SUMMARIZECOLUMNS(‘Stock Item'[Color])

Below is an example of my ODC file where I have replaced the values as shown with my example above. After the details I will explain what details I have put into the ODC file.

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Cube>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content="1.WWI Sales - 2020-05-27">
<meta name=Table content=Model>
<title>1.WWI Sales - 2020-05-27</title>
<xml id=docprops><o:DocumentProperties
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns="http://www.w3.org/TR/REC-html40">
  <o:Name>1.WWI Sales - 2020-05-27</o:Name>
 </o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
  xmlns:odc="urn:schemas-microsoft-com:office:odc"
  xmlns="http://www.w3.org/TR/REC-html40">
  <odc:Connection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=MSOLAP.8;Persist Security Info=True;User ID=&quot;&quot;;Data Source=powerbi://api.powerbi.com/v1.0/myorg/PPU%20Testing;Update Isolation Level=2;Initial Catalog=1.WWI Sales - 2020-05-27</odc:ConnectionString>
   <odc:CommandType>Default</odc:CommandType>
   <odc:CommandText> EVALUATE SUMMARIZECOLUMNS('Stock Item'[Color])</odc:CommandText>
  </odc:Connection>
 </odc:OfficeDataConnection>
</xml>
  • LINE 8:
    • This is where I put my dataset name
    • EG: <meta name=Catalog content=”1.WWI Sales – 2020-05-27“>
  • LINE 10:
    • This is the name for the ODC connection when it is displayed in Excel. To ensure consistency I used the dataset name
    • <title>1.WWI Sales – 2020-05-27</title>
  • LINE 14:
    • Here I put in the dataset name again.
    • <o:Name>1.WWI Sales – 2020-05-27</o:Name>
  • LINE 20:
    • This is where I am putting in the Power BI App Workspace Name as shown below.
    • I also put in the Initial Catalog which is another name for the dataset name.

      <odc:ConnectionString>Provider=MSOLAP.8;Persist Security Info=True;User ID=&quot;&quot;;Data Source=powerbi://api.powerbi.com/v1.0/myorg/PPU%20Space%20Testing;Update Isolation Level=2;Initial Catalog=1.WWI Sales – 2020-05-27</odc:ConnectionString>

  • LINE 22:
    • This is where I am putting the DAX query

      EVALUATE SUMMARIZECOLUMNS(‘Stock Item'[Color])

Getting the DAX query into an Excel file using the ODC file

The next step is to get the DAX query into a table in Excel using the ODC file.

To do this I opened notepad and pasted in the above text as shown above.

I then saved it to the following location on my PC.

I saved it with the extension of ODC.

NOTE: The reason I saved it to this location is that it is the default location Excel saved the ODC files.

Now for me to create the Excel file I double clicked on the ODC file.

I then got prompted with the Excel Security notice, I clicked Enable.

If required you might be asked to log in, put in your credentials that you would use to log into the Power BI Service.

I then waited a few seconds and then I could see the table in my Excel file.

When I click on this table I can see that the table name is “Table_WWI_Sales”

Enabling the drop-down list with the Data Validation in Excel

The final step in my process is to then create the drop-down list which can then be used for my RLS table as shown below.

I highlight the column where I want to create the drop-down.

Then in the Excel ribbon I click on Data Validation and then Data Validation again.

I then clicked on the Allow to “List”

And in the source I put in “=INDIRECT(“Table_WWI_Sales”)”, this is my table name from above.

Now when I go back to my RLS table I can either type in the color name, or I can click on the drop down and select the color as shown below.

Summary

In this blog post I have shown how I created a table in Excel using a DAX query from a Power BI dataset.

NOTE: If you have an existing Excel spreadsheet that you want to enable the data validation, what I did was to first create a new Excel file from the ODC connection. Then open the existing Excel file and then copy the sheet from my new Excel file into the existing Excel file.