Office Scripts Preview is available for Excel Online only as of February 21, 2020 – if you have an E3 or E5 License. I’m using Office Pro Plus which is a flavor of an E3 license (not all services that come with E3 license). Office Scripts is using TypeScript which is a superset of Javascript. TypeScript was created by Microsoft in 2012.

Admin Access To Office Scripts

Your administrator can grant access to Office Scripts

Point your browser to admin.microsoft.com. At the username dialog enter the admin username (email address)

At the password prompt, enter the admin password

If the username and password are correct, you are now logged into the Admin Center

Show All

Click on the Show All Tab to reveal additional options

Settings…Settings

Click on Settings chevron to expand the menu. Then click on Settings below Settings (confusing)

Services…Office Scripts

On the Services Menu, scroll down to Office Scripts

Tick the box “Let users automate their tasks in Office on the web” and click on the button “Save changes”

Automate Tab

Go to office.com, login click on the Excel App and click on New blank workbook

Note the new tab above the Ribbon “Automate”

When you click on the Automate Tab you will see the Record Actions (Macro Recorder) and the Code Editor Icons. Here, I clicked on the Code Editor. Quite different from the VBE (Visual Basic Editor).

Microsoft Docs Reference

Good reference material for Office Scripts on Microsoft Docs to get you started.

What Do Office Scripts Mean For VBE (Visual Basic Editor) ?

My best guess is that once Office Scripts is rolled out for the desktop, the development will take place in a different IDE (Integrated Development Environment) such as Visual Studio or Visual Studio Code

What Does It Mean For Python?

Python as a scripting language for Excel (Office) is the #1 upvoted item on Excel User Voice at 6,342 votes as of February 22, 2020. As of February 22, 2020, Python is the #3 programming language in the world, JavaScript is #7 according to the Tiobe Index. Maybe Office Scripts will help to propel JavaScript? Or maybe one day we will see Office Scripts/TypeScript as a separate entry on the Tiobe Index?

Tidy Up

That’s it for today. Post inspired by Bill Jelen’s (aka Mr Excel) Podcast. Make sure you check it out.

, , , , , , ,

Another classic quip from Mark Twain (Samuel Clemens). Perhaps not one of his better known, but apropos for displaying numbers.

Power BI Desktop Version

I’m using version 2.76.5…. released ~ 12/18/2019. There are no newer updates as of 2/15/2020. If you are using a different version, your results may vary.

Load Some Data

I grabbed some population data from Wikipedia to look at US Population percent change by state from 2010 to 2019. I had to clean the data a bit because it included Totals, Region Totals, and Territories – I just want the 50 US States.

I’ll see what happens if I try to change the Data type of the Pcnt Change column in the Data Model

  1. Click on the Data icon in the x pane
  2. Click on a cell in the Pcnt Change column
  3. On the Modeling Ribbon, click on the drop-down for the Data type.
  4. Change the Data Type from Text to Decimal Number

You will receive a Data type change warning. Go ahead and click on the Yes button to confirm the change

Note the difference in the appearance of the data in the Pcnt Change Calculated Column. Before the change, the data is left-aligned, after the change, the data is right-aligned.

Format as Percentage (%)

Now that I have a decimal value in the Calculated Column, I’ll change it to a percentage. On the Modeling Ribbon, in the Formatting Group

  1. Click on the % icon
  2. In the decimal places drop-down, I changed from 2 decimal places to 1 decimal place

Formatted Tooltip

Now the tooltip is nicely formatted and easy to ready without 9 decimal places.

Tidy up

That’s it for today. Grab the workbook from my OneDrive. Post inspired by this post at PowerBI tips by Mark Carlo and Seth Bauer.

, , , ,

There is an update for Power BI Desktop available this morning, Tuesday, February 18, 2020. Download is here. No official announcement on the Power BI Blog as of this morning.

, ,

In Part I of this series, I demonstrated how to use Range Objects, Arrays and VBA to Filter and Delete data from a table. Today, I’ll use Power Query to Filter data from Table A based on criteria in Table B.

Load Data

I’ll begin by loading data from an Excel Workbook to Power Query in Excel. Here is the data I created in Part I of the series where I grabbed NFL winning percentages for all 32 NFL Teams. I replicated the data 9 more times giving me a total of 320 records.

Just like in Part I, I want to filter out the 4 teams in the NFC East. So, if everything works correctly, I should have 280 records when I am done.

Load Data Into Power Query

In Excel, click on the data menu (1). In the Get & Transform Data Group A(2), click on the Get Data icon (3).

In the pop-up menu, click on From File (4). From the next pop-up menu, select your file type. I’m using an Excel Workbook as my data source, so I’ll select Excel Workbook.

From the Import Dialog Window, navigate to the file that has your data, highlight the file (6) and click on the import button (7).

In the Navigator Dialog Window, tick the box next to “Select Multiple Items” (8). Next, tick the box next to the name of each worksheet you wish to import into Power Query (9). In this instance, there are 2 worksheets and I want to import both worksheets. Lastly, click on the Transform Data Button (10).

The data is now loaded into Power Query. There are two tables showing in the queries pane. The table with all records is named “Data” (Table A) and the table with the 4 teams I wish to filter out of the data are in the table named “crit” (Table B).

Now that I have all data that I need loaded to Power Query, I want to perform any transformations on the data and/or tables that are needed before I can use Table B as a filter on Table A.

Transformations

Power Query added a few transformations when I imported the worksheets into Power Query. The other transformation I need, is to convert the criteria table (Table B) into a List.

  1. Click on the crit Table in the Queries Pane
  2. Click on the Transform menu across the top of the Power Query Window
  3. Click on the Convert To List icon in the Any Column Group

The crit table (4) has been converted to a list (5). Note the different icon in the queries panel.

Filter Manually

Let’s take a look at what happens when I deselect the 4 teams that I want to remove from the filter drop-down in the Data Table. in the Text Filters drop-down (1), I removed the tick marks for the 4 NFC East teams (2)

A new Applied Step is added to the Applied Steps Pane (3). There are now 280 records (4) which is what I was after.

All well and good, however I want to use the list as the list of items to exclude, I don’t want to manually deselect teams. It is easy enough for this example, but imagine if I have a much larger table with many more items I need to exclude. Let’s take a look at the expression created when I manually deselected the 4 teams:

= Table.SelectRows(#"Changed Type", each ([Team] <> "Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"))

The expression is telling Power Query to return all Rows except those where the team is Dallas Cowboys, New York Giants, Philadelphia Eagles, or Washington Redskins. Those are the 4 teams I have listed on my criteria list “crit” so I need to replace

"Dallas Cowboys" and [Team] <> "New York Giants" and [Team] <> "Philadelphia Eagles" and [Team] <> "Washington Redskins"

with

List Contains

Also note in the expression, that Power Query is using the Applied Step Name #”Changed Type” in the place of the Data Table, so I will want to use the same Applied Step in my revised expression:

= Table.SelectRows(#"Changed Type", each List.Contains(crit,[Team])=false)

That is much easier to read. Note that I used =false to exclude the teams from the crit list. Here is the data table with the revised expression showing the 280 records as expected.

List Changes – Data Updates

The idea is to pass a dynamic list to filter the data table so I’ll check to see what happens to the data table when the list changes. I added the Cincinnati Bengals to the data source, saved and closed the workbook.

I returned to the Power Query Editor and refreshed all to update the list and the table. I added a fifth team so the list should return the 5 teams and the record count in the data table should now be 270.

I now have a List (Table B) that is filtering a Table (Table A). The expression now refers to a a list so that the filter is dynamic – as the data source expands and/or contracts, I can refresh all and the Data table will show more or fewer records depending on the teams listed in the data source.

Power BI Desktop

The steps are the same if you are using Power BI Desktop.

Tidy Up

That’s it for today. Grab both files from my OneDrive

Stay tuned for Part 3 in the series.

, , , , , , , ,

See the announcement from Joe McDaid, Program Manager, Excel. I think a few of the limitations of VLOOKUP that Joe points out are easily overcome with well structured data and strongly typed formulas, but XLOOKUP is a great addition nonetheless.

, , , , ,