Refreshing Power Pivot Excel Data Models That Use The Current Workbook As A Data Source In Excel Online

Something that got lost in all the excitement around Excel reports connected to Power BI datasets working in Excel Online is the fact that Excel reports connected to the Excel Data Model – aka Power Pivot – now work too (although I did mention it here). Right now it’s not possible to refresh the data stored in the Excel Data Model if you are connected to external data sources. However, today I noticed something nice: if you are using a, Excel table in the same workbook as a source for a table in the Excel Data Model, if you make a change to the table in the worksheet then it is possible to refresh the data in the Excel Data Model.

Here’s an example. In Excel on the Desktop I created a new workbook and added a simple table to a worksheet:

I then moved to the Power Pivot tab on the ribbon and clicked the “Add to Data Model” button to add this table to the Excel Data Model:

I then created a PivotTable connected to the Excel Data Model:

After saving the workbook to OneDrive for Business, I closed it in Excel Desktop and reopened it in Excel Online in the browser:

Finally I was able to change a value in the source table, click the Refresh Selected Connection button on the Data tab in the ribbon, and see the change reflected in the PivotTable connected to the Excel Data Model:

Of course it would be better if you could refresh external data sources too but I still think this could be useful, for example if you had multiple users updating forecasts or budgets in an Excel table in the browser and were using the Excel Data Model for reporting on this data.

Handling “Select All” For Slicers Bound To Dynamic M Parameters In Power BI

Last year I blogged about handling multi-select scenarios for dynamic M parameters. Since then support for “Select All” has been added (see here) but since a regular slicer with no items selected behaves the same as one with all items selected, some extra logic is needed to make a slicer bound to a dynamic M parameter behave like a regular slicer.

To illustrate this, I created a simple dataset with two tables in it:

  1. A DirectQuery table linked to the AdventureWorksDW DimDate table (a simple date dimension table), stored in Snowflake
  2. An Import mode table called Day Name that contains one column and seven rows containing the names of the days of the week

I then created an M parameter called DayParam and bound it to the DayName column of the Day Name table and enabled the Multi-select and Select all options:

Here’s the M code that shows how to use the DayParam parameter to filter the DimDate table on the EnglishDayNameOfWeek column, and handle the scenario where “Select All” has been selected as well as the scenario where no item has been selected:

let
  Source = Snowflake.Databases(
    "xyz.snowflakecomputing.com",
    "DEMO_WH"
  ),
  AWORKS_Database = Source
    {
      [
        Name = "AWORKS",
        Kind = "Database"
      ]
    }
    [Data],
  PUBLIC_Schema = AWORKS_Database
    {[Name = "PUBLIC", Kind = "Schema"]}
    [Data],
  DIMDATE_Table = PUBLIC_Schema
    {[Name = "DIMDATE", Kind = "Table"]}
    [Data],
  #"Filtered Rows" =
    if DayParam = null then
      DIMDATE_Table
    else if Type.Is(
      Value.Type(DayParam),
      List.Type
    )
    then
      if DayParam = {"__SelectAll__"} then
        DIMDATE_Table
      else
        Table.SelectRows(
          DIMDATE_Table,
          each List.Contains(
            DayParam,
            [ENGLISHDAYNAMEOFWEEK]
          )
        )
    else
      Table.SelectRows(
        DIMDATE_Table,
        each [ENGLISHDAYNAMEOFWEEK]
          = DayParam
      )
in
  #"Filtered Rows"

The #”Filtered Rows” step does the following:

  • If nothing is selected in the slicer then the DayParam parameter will contain a null value, and in this case no filter will be applied to the DimDate table
  • If the DayParam parameter is of type list (when there is a multi-select in the slicer) then
    • If it’s a list containing a single text value of “__SelectAll__”, the default value returned when the slicer has “Select all” selected, then no filter will be applied to the DimDate table
    • Otherwise List.Contains will be used to filter the DimDate table by all the selected values
  • Otherwise the DayParam parameter will contain a single scalar value (because a single value has been selected in the slicer) and the DimDate table will be filtered by that value

Here’s how it works:

Multi-Value Parameters In Power Query Online

There’s a nice new feature in Power Query Online (the version of Power Query used in Dataflows): parameters of type List. You can see this as a new option in the Type dropdown in the “Manage parameters” dialog:

Why is this interesting? In the past, Power Query parameters were always single values like a date or a string; now a parameter can contain mutliple values.

There’s one other new feature in Power Query Online that goes along with this: In and Not In filters, which can use these new List parameters.

I’m sure there are other cool things you can do with this but I’ll leave them to future blog posts.

Power BI Report Accessibility: Emulate Vision Deficiencies Using Edge DevTools

I’m not an expert on Power BI report accessibility like Meagan Longoria but I do know how important an issue accessibility is. I also know how difficult it can be to remember to check for accessibility issues when building reports which is why, when I was watching this video on new features in Edge DevTools, I was pleased to see that Edge now makes it easy to see how your report looks like when viewed by someone with vision deficiencies.

Full documentation is here but since this is a feature aimed at web developers rather than the needs of a Power BI developer, here’s a quick summary. All you need to do is open your report in Edge, hit Ctrl-Shift-I to open DevTools, go to the Rendering pane (you can find it by clicking on the chevron icon in the top menu):

The Edge DevTools menu and how to find the Rendering pane

…and then going to the “Emulate vision deficiencies” menu:

The "Emulate vision deficiencies" menu

Here’s a demo of what it does:

Animated gif showing how the "Emulate vision deficiencies" menu affects a Power BI report
Of course this isn’t the only thing you should be checking for regarding accessibility (see here for a more comprehensive list, for example) but having this feature built into Edge does remove a lot of the friction around making these checks.
%d bloggers like this: