Power BI and SharePoint – problem with new query

While creating queries with Power BI Desktop and also from within Excel, one thing had me stumped: When trying to query a particular SharePoint 2010 site, I would get an error.

The feed's metadata document appears to be invalid.

“OData: The feed’s metadata document appears to be invalid.”

Other SharePoint sites in the same site collection offered their lists just fine. I started a Fiddler trace to see what was going on, and it showed that there was a problem with the ListData service metadata file that has the URL “http://<SitePath>/_vti_bin/listdata.svc/$metadata”. I plugged that URL into the browser and here, too, instead of presenting the XML of the metadata, I saw this error “An error occurred while processing this request.”:

ListData.svc with broken metadata

It took a lot of searching and head scratching until I finally worked it out. I came across a blog post on Technet:  why would listdata.svc return an error that explains how invalid characters in a column name can cause problems for the list service. I’m religiously avoiding special characters when naming columns, but I thought I’d take a look through the site’s lists.  I found a list with a name that started with a number. While numbers are not exactly special characters, there are places where they can cause problems. Excel’s range names cannot start with a number, either, for example.

I renamed the list to start with a letter and refreshed the metadata tab. Lo and behold!

Listdata.svc with working metadata

The SharePoint internal name has not changed, but the XML contains the renamed version. It appears that a list name starting with a number breaks the metadata view of a list in SharePoint 2010.

After closing Excel / Power BI Desktop to clear each query cache, the query into that SharePoint site worked fine from both tools.

Problem solved.

PS: I tried the same in SharePoint 365 and could not reproduce the problem there. The metadata page opens fine, but the name of the list that starts with a number appears like “C_123MyList”.

Share this:

Fibonacci Clock with Excel – no code

The Fibonacci Clock is getting quite some attention on the web lately. Check it out here and read about how it works.

Short summary: The clock consists of five squares with different magnitudes: 5 – 3 – 2 – 1 and 1 again.

Add up the magnitudes for the red and the blue squares to arrive at the number of hours.

Add up the magnitudes for the green and the blue squares, multiply the result by 5 to arrive at the number of minutes.

Ignore white squares.

Minutes are rounded to the nearest 5. AM and PM don’t exist.

So, the time 9:25 looks like this:

Red and blue squares are magnitudes 5 + 3 + 1 = 9. That’s the hour.

Green and blue squares are magnitudes 3 + 2 = 5. Multiply by 5 = 25. That’s the minute.

Not everybody may be willing to pay for the hardware version of the clock, but even if you will get one, you will need some practice to get comfortable reading what it says. So here is your free Fibonacci Clock created with Microsoft Excel.

This is really great and a major advancement. Instead of just glancing at the clock and knowing instantly what time it is, you now need to spend upwards of 5 seconds, maybe up to minutes, to work out what the time is by adding up colour-coded squares. Yep. It’s for nerds. Geeks welcome.

All you need is a working version of Excel on your device. It will work on Excel for your computer (PC or Mac), Excel for iOS, or Android, and with Excel on-line, because it only uses conditional formatting and a few formulas. There’s no VBA code involved. Here’s a screenshot of the file on my iPhone:

Many time stamps can be represented with various combinations of the squares. I’ve used formulas and conditional formatting to arrive at a combination that has the correct time and not too many blue squares.

Download the attached file and play with the settings. You can use the system time and edit any cell to refresh the clock, or enter a time manually and practice how to read it.

Download the Fibonacci Excel Clock

Share this:

Power BI online – the WOW! factor

I’ve been playing with the Power BI Preview package (that is the thing that runs on your online 365 subscription, NOT the Power BI for Excel things), using my company’s E++ 365 plan.


I’m quite impressed. (Now, here’s an understatement)


There’s Power BI online where you can upload or otherwise link to data sources and then create reports and dashboards.


You can also download Power BI Designer and do some preliminary work on your local machine before sending it to the cloud.


The Power BI Designer is like Power Query bundled with Power View, but without the hassle of Excel. Very cool. I can copy/paste M code from my Excel_with_Power_Query files straight into the Power BI Designer and — swoosh — … all my data is available to be used in dashboard visualisations. I don’t have to write them from scratch. Yay. Saves a lot of time.


I can build a report using the point and click options. Tables, charts, combos. Nice.


I can save the whole shebang as a pbix file and then import (connect > upload)  that pbix file to the online Power BI site. Verrrry nice. I have many MBs of data in my scenario, but the pbix file is only a few MB tall. 


It took me less than 30 minutes to get company proprietary data from a SQL database into the Power BI cloud, showing in an attractive dashboard that can only be accessed by people I personally invite. 

That is so totally, awesomely cool,  that this line should be printed in 72pt font. 


The execs of my company can download the iPhone app for Power BI and connect to the dashboards that I’ve shared with them. They can start with the overview and drill down into the data with a very cool and very intuitive interface. 


Get that.


My CEO can see a dashboard I create on my puny company issue PC minutes after I upload it to our Power BI site. She can use a browser in an internet cafe or her iPhone. She can drill into the data and, on the browser interface, ask questions like “show the total of all sales in the x region for the year z” and the dashboard will add a tile with that data.




Congratulations to the team who designed that. 

Share this:

Vlookup – why do I need TRUE or FALSE?

In the last few days there has been a discussion about VLookup and how the last parameter works when it is entered as TRUE, FALSE or omitted altogether.

The Microsoft Help page was showing an example screenshot, where the formula and the result did not match up.

This was an oversight in the help documentation and will be fixed.

But it sparked a few questions, like:

  • Why do I get an error for this formula, but when I expand the lookup table by one row I get the correct result?

With the last parameter set to 0 or FALSE, Vlookup will go through the list from the top, one cell after the other, and will compare each value in the first column to the lookup value. The list does not need to be sorted. When it finds a match, it returns the value from the specified column. For large lists, this can be rather slow.

Vlookup with the 1 or TRUE as the last parameter (omitting the parameter defaults to TRUE), works differently. Vlookup will now assume/expect that the list is sorted ascending in the first column. In order to find the lookup value, it does not inspect each cell, but cuts the list in half. If the value at the half point is a match — Bingo! If not, it compares the value at the half point with the lookup value and decides: If the lookup value is greater than the value of the half point, then continue with toe lower half of the list. Cut the lower half of the list in half and see. If the lookup value is smaller than the value of the half point, then continue with the upper half of the list. Cut the upper part of the list in half and see.

So Vlookup will start with 1/2 of the list,  then reduce to 1/4 of the list, then to  1/8,  then 1/16, etc. until it finds the match. If it gets down to only one value, but that value is not an exact match, this value will be treated as the match and the corresponding column value will be returned.

Are your ears smoking yet? Let’s look at an example.

Consider this table

The formula in A12 is =VLOOKUP(“h”,A2:B10,2).

Vlookup will split the table in half and will look at the last value of the top half. If there is an uneven number of rows, it will grab the next row, too. So Vlookup will consider this range:

The table has 9 rows, so Vlookup looks at the fifth row of the data table.

With text values, “greater” and “smaller” refer to the position in an alphabetic sort. So “a” is smaller than “c” and “k” is greater than “c”.

The value “i” is greater than the search value “h”. Therefore, the desired value is above Excel row 6. Vlookup will then split this upper part of the table in half. Since there are 5 rows involved, the split will be done at the third row of the range:

Vlookup looks at Excel cell A4. This value “e” is smaller than the lookup value. That means that the value we want is not in the table range that was just inspectedd, so we can forget that part of the table. Next Vlookup looks at the lower half of the range from the previous step.

Again, the last value in that range is “i” and greater than the lookup value, so let’s split this range in half and look at the last row of the top half:

This range consists of one row only. There’s nothing more to split. The value is not an exact match, but the return column will be returned, anyway.

Vlookup with TRUE as the last parameter will deliver the value that is “equal to or smaller than” the lookup value.

Coming back to the example with the names at the top of the post:

The formula used is =VLOOKUP(“Akers”,B2:D5,2)

The range is split in half, the last value in the top half is greater than the lookup value, so the lower half of the table will not even be considered for further inspection. The top two rows will be split in half and a value smaller than “Carido” will be expected in the last cell of that next split. But the last value in a split has “Weiler” as the value. That is not smaller than the lookup value “Akers”. Hence an error is returned.

If we add one row to the lookup table range, the first pass of Vlookup will split the table of 5 rows in half, rounding up to contain the top three rows in the first pass. Now we use the formula


The value in cell B4 is a perfect match, so the value in column C is returned. Bingo! We have a correct result for an unsorted table, without the fourth parameter!

Magic? No, it is pure coincidence. Omitting the last Vlookup parameter on an unordered list MAY return the correct result, but the chances of that are rather slim.

In this case, it is pure luck that the lookup value matches the last entry of the range that Vlookup considers in this pass.

If you use Vlookup without the fourth parameter, you must ensure that the lookup table is sorted by the first column. Otherwise, any result you get back is as reliable as getting the lotto numbers from a toddler.

It may work, but in most cases it won’t.

Share this:

Office for iPad – a first look at Excel

Just installed Excel for iPad. I have an iPad 2 running iOS 7 and installing Excel from the App Store went quickly and smoothly.

Opening Excel on the iPad for the first time, I found the user interface clean and intuitive. After playing with it for a few minutes, here are my first impressions.

  • It’s easy to connect to your OneDrive, but you can also store files locally and use them off-line
  • The insert ribbon contains commands to insert tables, charts, pictures (from the iPad Camera Roll), shapes and text boxes. It even shows a button for Recommended Charts. The Charts button has every familiar Excel chart that you know from the full desktop version.
  • the Formulas ribbon contains buttons for the different formula categories and a little calculator icon on the right to recalculate the sheet.
  • When a table, chart, shape etc. is selected, a context sensitive ribbon tab appears with an impressive array of commands for the selected item, including styles, colors and much more.
  • Although data validation and conditional formatting cannot be defined on the iPad (as far as I can see), they work great if a file already has conditional formats or data validation set up.
  • If named ranges have been defined in a workbook, these work in Excel for iPad and they can be used to create new formulas on the iPad.
  • Excel for iPad supports freezing panes, either first row, first column or both.

Here’s a screenshot of a file I created on my computer and then emailed to myself. I started off with this:

Workbook prepared with Excel 2013 for Windows

Workbook prepared with Excel 2013 for Windows

I added some data, created a table from that data and created a chart from the table. Added a shape, moved the data validation cells to a different position, changed numbers in the conditionally formatted ranges and added a new formula using named ranges and the =Formulatext(C13) to show the formula I added.

Excel on iPad

Excel on iPad

Here is what it looks like with the data validation drop down opened. The screen is overlaid with grey and the data validation box really stands out.

Excel on iPad

Excel on iPad

VBA and external data sources are not supported in this release. When opening a file that contains VBA or links to external sources, a friendly message appears that informs the user, but after that the file will open fine.

Overall, I am very impressed with this Excel experience for iPad. You would not want to create complex models from scratch using just iPad, but it is now possible to take your Excel files with you, store them on your iPad and work on them off-line, doing more than just simple data edits.

Addendum Sunday 30th March 2014: This review is a very quick recap of how Excel for iPad deals with some of the most frequently used Excel functions. Bill Jelen, aka MrExcel has a review inspecting much more comprehensive scenarios here.

Share this:

Slides and files from MVP Com Camp – Power Map

Thanks to all the interested people who turned up at this fantastic event today in Auckland. It was a glorious day and I would not have blamed you if you had chosen the beach over a conference room.

I’ve posted my slide deck and all the files I demoed or used in videos in my OneDrive here: http://bit.ly/PZOrZm

Darrell took a few photos. I’ve pinched these:




Share this:

SharePoint: Redirect if user clicked a button previously

I just came across this question in sharepoint.stackexchange.com. When a user visits a SharePoint site, they are presented with a splash screen and need to accept the policy before they can proceed. Upon subsequent visits, the splash screen does not show, because SharePoint will remember the  user.

PirateEric outlined a possible solution: Use a SharePoint list to save the user name when the button is clicked. When the page loads, look up the user in the list. If they already exist, redirect the page, if not, show the splash page with the button to accept the policy. If the policy changes and users need to be made aware of that, simply remove all items in the list that tracks the users. All this can be done with jQuery and web services.

That intrigued me and I had a go at actually building this, using Marc Anderson’s SPServices.

How to set it up

Create a SharePoint custom list with two fields, Title and UserName. The former is the out of the box field, the latter is a simple text field.

Create two pages, the Splash page with the button and the page that is the desired destination page for all visitors. In my sample these are called Splash.aspx and MainContent.aspx

On the Splash page the code that you can see below will be loaded before any other web part. If you use a Content Editor Web Part to load the code with a content link, make sure that it’s the first web part on the page.

In many cases, JavaScript and jQuery will be placed in the last web part of the page and run after the DOM has loaded. But in this case this would mean that the Splash page appears briefly, even if it is followed by a redirect to a different page.

The Splash page provides the policy (or terms and conditions) that the user must accept, and a link or a button that the user can click to accept. This link or button must then trigger a JavaScript function. That is very easy to do. I used a button and put the html straight into a CEWP like this:

<button onclick="PolicyButtonClick()" type="submit">
   I accept the policy

So the user clicks the button and the JavaScript function PolicyButtonClick() will run. This function can be found in the code below.

First, the jQuery library and the SPServices library are loaded. Then the user name of the current user is retrieved with the SPServices call using SPGetCurrentUser. It returns a text string in the format DOMAIN\Account.  Next, the SPServices call uses the GetListItem. In the call, a CAML query is constructed that will return only list items where the column UserName equals the current user.  The items returned by that query are then counted. Since the user account is a unique value, we can safely assume that the query will either return one result or no result at all.

If the query returned an item, this means that the user has previously accepted the policy and the script will redirect to the MainContent.aspx page.  If the query did not return anything, the current page will continue to be displayed.

When the user clicks the button to accept the policy,  the user name is written into a variable. Then the SPServices operation to UpdateListItem is called and will create a new item in the list “PolicyAccepted”, storing the previously established account name in the column UserName. Then the MainContent.aspx page is loaded.

The next time the user opens the Splash page, their account name will be found in the PolicyAccepted list and they will not see the Splash page again, unless the entry in the list is deleted.

Here is the complete script:

<script language="javascript" type="text/javascript" src="/path/jquery-1.10.2.min.js"></script>
<script language="javascript" type="text/javascript" src="/path/jquery.SPServices-2013.02a.min.js"></script>
<script type="text/javascript">
// start the code even before the DOM is loaded, so not waiting for document ready
//$(document).ready( function() {
// get the user name
 var userName= getUserName();
// find the user name in the list
 var userAccepted = matchUserName(userName);
 if (userAccepted == 1 )
 // redirecting page

function getUserName() {
 var thisUserAccount= $().SPServices.SPGetCurrentUser({
 fieldName: "Name",
 debug: false

function createNewItem(theTitle, theUser) {
 operation: "UpdateListItems",
 async: false,
 batchCmd: "New",
 listName: "PolicyAccepted",
 valuepairs: [["Title", theTitle], ["UserName", theUser]],
 completefunc: function(xData, Status) {

function matchUserName(userName) {
 var queryText = "<Query><Where><Eq><FieldRef Name='UserName'/><Value Type='Text'>" + userName + "</Value></Eq></Where></Query>";
 operation: "GetListItems",
 listName: "PolicyAccepted",
 async: false,
 CAMLQuery: queryText,
 completefunc: function (xData, status) {
 itemCount = $(xData.responseXML.xml).find("rs\\:data, data").attr("ItemCount");

function PolicyButtonClick() {
 var userName= getUserName();
 var theTitle= "Accepted";
 createNewItem(theTitle, userName);
 window.location.href = "http://path/Documents/MainContent.aspx";



Share this:

List all files in folder to Excel

I’m currently working on a project where I need to analyse documents, so they can be properly classified and uploaded to a SharePoint based document management system. Looking at the folder structure with Windows Explorer does not really help much. I need a list of files in their respective locations, so I can sort and filter, pivot and drill down.

There are some VBA code snippets out there which create reports of files in folders and sub folders in Excel, but none of them really do what I need, so I listed out my requirements and re-wrote some code originally posted by Leith Ross at Excelforum.com.

My requirements:

  • Specify a starting folder
  • Have all files and all sub folders listed out, or just the starting folder
  • Show starting folder in one column and the sub folders in separate columns each, so I can create pivot tables, sort and filter on the folder location
  • Show file name, file extension, file size, and core dates in separate columns, so the results can be sorted and filtered
  • Highlight large files with conditional formatting in three colours for > 2MB (yellow), >5MB (orange) and >10MB (red)
  • Keep a column with the original sort order (=location of the files)
  • Enable unlimited levels of nesting
  • Create the report in a new file, so I don’t have to clean up the original

I created an Excel table with these columns:

  • Order
  • File name
  • Extension 
  • Created
  • Modified
  • Size
  • Size MB
  • Path
  • Level1
  • Level2
  • … through to Level20

The table has one row of dummy data in which conditional formatting and one formula for the conversion from Byte to MB is defined. (Yes, I know I could do the conversion with VBA, as well).

Another sheet contains the user interface. It has just one big button to start the macro, which then

  • prompts the user for a starting location, which must start with a drive letter in the format “Z:\” and can contain a longer path.
  • checks that the second character of the location is a colon. If not, it cannot be a valid drive location.
  • prompts the user whether or not to include sub folders (just in case you only want to list out one folder)
  • goes off to work and gathers all the information, writes it into the table, splitting out the data into the columns
  • provides a status bar message with the folder that is currently in progress (the macro can run for quite a long time, depending on the number of files returned)
  • copies the report to a new workbook and removes the placeholder row in the table
  • closes the macro enabled file without saving, so nothing needs to be cleaned up.
If a folder has more than 20 nested sub folders, the macro will still work and write the folder names into the next available column, but the header of the table will have to be added manually.

The file can be downloaded here: CreateFileReport.xlsm

If you want to make changes to the original, make sure to save the workbook before you run the macro, or edit the code and comment out the row that closes the original workbook without saving.






Share this:

Control-Shift-Enter — Win your copy

Ctrl+Shift+Enter - Mike Girvin's book about array formulas

Ctrl+Shift+Enter - Mike Girvin's book about array formulas

Mike Girvin’s new book about array formulas called “Ctrl+Shift+Enter” is something you won’t want to miss if you are proficient with Excel formulas and want to get to the next step. Amazon introduces it like this:

Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions.

You can win one of four free e-book copies of the book if you enter the contest at Debra Dalgliesh’s site here.

Hurry. The competition closes on August 7th, 2013, at 12 noon Eastern Daylight Time.

Share this:

Percentage charts with conditional formatting

Last week I saw some amazing Excel spreadsheets while I was attending the MVP Summit in Seattle and I thought I’d share some ideas. So here are step by step instructions on how to create a percentage chart with just the Excel grid and a sprinkle of conditional formatting. The goal we’re after looks like this:

We’re looking at a grid of ten by ten cells that form a square (roughly). Each cell in this grid represents 1% of the whole. The number below the grid is represented visually by an according number of filled squares (or cells).

Here is how to do it:

Build the grid

First we need the numbers from 0.01 to 1.00 in steps of 0.01 in a grid of 10 by ten cells. If you want, you can type these 100 numbers in manually, but there’s a faster way if you use the fill handle (the little dark square at the lower right corner of the active cell). We only need to type in four numbers. Let’s start in cell C4 and enter 0.91, then manually enter 0.92 in D4. Cells C5 and D5 will get the numbers 0.81 and 0.82 respectively.

Select all four cells, then grab the lower right corner of cell D5 and drag it all the way across to cell L5.

Release the mouse button and you will see the numbers filled in and incremented accordingly:

Leave the cells selected, grab the fill handle in L5 and drag it down to row 13:

Release the mouse button and your 10 x 10 grid is filled with the correct numbers.

That was a bit faster than typing them all in, right?

Prepare the Total cell

Leave one blank row below the grid, then select cells C15 to L16 and use the command Merge cells from the Alignment group of the Home ribbon.

After merging the cells, let’s enter a number, say 38% and do some number formatting. I’d like the cell to show the text “Total:” as well as the percentage number. Click the lower right corner of the Number group on the Home ribbon to open the Format Cell dialog. In the left hand pane select Custom at the bottom of the list. In the Type field enter the text
Total\: 0%

Note: The “\” in front of the “:” sign is needed make sure it appears correctly. If we did not add this, Excel would get confused, since the “:” has a special use in time formats

Hit OK and see how the cell shows text and number, even though the formula bar clearly shows that the cell only contains a number. Crank up the font size a bit. In this example I have used 28 pt Century Gothic.

Format the grid

Now we need to apply some TLC to the grid. Let’s hide the spreadsheet gridlines. On the View ribbon, untick the Gridlines box and the spreadsheet appears in uniform white.

The 10 x 10 grid should have light grey borders, so select the 100 cells …

… and open the Format Cells dialog on the Border tab. Select a light grey colour from the colour drop-down and after that click the Inside and Outline boxes.

The cells now feature a faint grey grid, but the numbers are still showing. Here’s how to hide them for good: Keep the 100 cells selected, open the Format Cells dialog again and activate the Number tab. Choose the Custom option from the number categories and in the Type field enter three semicolons


A number format in Excel consists of four parts to define positive, negative, zero and text values. The different parts are separated by semicolons. If the format definitions between the semicolons are missing, then the number will not be shown at all.

Now the grid is empty and we’ll set row height and column width to make it as close to square as possible:

Select columns C to L and use the Format drop-down on the Home ribbon, where you will find the Width command. Then select rows 4 to 13 and use the same dropdown to select the Height.

Apply Conditional Formatting

The last step is the conditional formatting of the 100 cells. Again, this can be done in one go. Select the grid, then click the Conditional Formatting drop-down on the Home ribbon and select New Rule.

In the next dialog click Use a formula to determine which cells to format. In the formula box enter this formula:


Note the position of the $ signs. Only the $C$15 should have them. Don’t put them anywhere near C4.

Click the Format button and select a darker grey as a fill colour.

Hit OK on all open dialogs.

That’s it. You’re done.

Change the number in the merged cell below the table and see the grid update with more or less grey blocks.

Of course, you can use other colours. Look at my Fruit Machine below. Click the blue cell and hit the Delete key to make the numbers change. Can you get all of them to display the same number?

Depending on your browser and/or screen resolution, you may see some of the grid lines wider than others. To download the workbook just click on the Excel icon in the dark bar below the spreadsheet.


cheers, teylyn

Share this: