Recently, one of my clients pinged me because she wasn’t seeing any items in a list she knew had many items in it. She’s used the list many times in the past and she hadn’t changed any of the view settings.
I did all the usual stuff:
Checked permissions on the site: She was a Site Owner, as we both thought.
Looked for broken inheritance on permissions – unlikely to be the problem, since she was a Site Owner.
Restarting the browser
Rebooting the machine
A bunch of random stuff
I could see all the items in the list, as could a few others.
It seemed like such a simple thing, but nothing in my normal arsenal was solving the problem, so I turned to Bing.
There were some…interesting…solutions there, but the winner is simple:
Navigate to List Settings, then Advanced Settings
Scroll down to Offline Client Availability – Odds are it will be set to Yes, so change it to No and save.
Go back to the list and you should see the items again. If you’d like to (and I’d suggest you do), go back in and set Offline Client Availability to Yes again.
My theory is something happened to the caching for the list.
Guess what? The first post in the thread above was just about the same time offline mode rolled out for Microsoft Lists: Microsoft Lists gets ‘supercharged’ performance and offline support | Windows Central. If you didn’t know this ever happened, then it’s working exactly the way it’s supposed to work. If you lose Internet connectivity for a short time, you can continue working and the changes sync when you’re connected again. Heck, take your laptop to a desert island for a week and work on your list and your changes should sync when you plug in again.
When I’ve built solutions which rely on caching in the past, I’ve usually hidden a “cache buster” button somewhere in them. Caching sometimes goes sideways on you, no matter how good your code is. The “cache buster” button here was an advanced setting few people would even think of, much less know about. It would be great if there were some indication on the screen somewhere that maybe something is awry, but no such luck.
Leaving this here for anyone who might be as frustrated as I was, maybe even future me.
SharePoint lists & libraries: we all love ’em. With multiple views, they are like little apps.
[Why the heck do lists have a brand and logo but libraries don’t? And why aren’t the two just considered simply variations on a concept by Microsoft?]
Do you find yourself using both the modern & classic view settings? If so, why do you continue to use the classic view settings? I’m building a list (ha!) for the Microsoft folks. I’m after feedback about view settings in particular, but anything you find yourself returning to the classic settings UIs for regularly would be helpful.
For example: I go to the classic view settings to display the Title in libraries or to create a “folderless” view.
Here’s what I’ve collected so far. Please reply in the comments and let me know what we’re missing! I’ll keep adding to this post as I hear from more folks. You can help by spreading the word by amplifying my posts on Twitter, Mastodon, and/or Facebook. I’m consolidating and rewording for consistency – my apologies if I mangle what any of you said. Just let me know if I’ve missed anything!
List Settings
Manage Content Types (Order, Field Hidden)
Access to field to see internal name in the url parameters
Manage View Settings
Reindexing List “if you want to include them in the Search”
Versioning
Item permission “Create items and edit items that were created by the user”
To deploy add-ins / command extensions to the app catalog. You can’t deploy these in the modern view.
Managing permissions – Permissions for the list/library are much clearer. The Shared/Shared With is too awkward.
Manage files with no checked in version
Setting Column Default Values
View Settings
Display the Title in libraries
Create a “folderless” view
Add the list ID field to a view
When I need to “Display items in batches of the specified size” and defeat the endless, maddening modern scroll-delay-scroll-delay nonsense for large libraries.
Easier to sort column order, filtering, grouping and sorting on one single screen, as opposed to clicking around on modern. Usually for when you setup a list which is more than a few columns.
You import from Excel anything more than 5 columns you are going to want to sort out without horizontal scrolling and dragging and dropping, etc.
For grouping/filtering, I find the classic UX easier than JSON editing [Several people have said something similar]
Classic for lots of reasons (reindex, permissions, versioning, open behavior, etc.)
Fields that are not displayed but are used in formulas. I find it more reliable to select them in the classic view settings.
Display items in batches of the specified size
Displaying the version number in the view
Getting the URL of the view [This one bugs me, too. When you switch views, you get a URL like /AllItems.aspx?viewid=97fbd6f4-82a9-4916-9a1b-65ce28328173. You can’t know what the actual view page is without editing the view – i.e., going into the classic view settings.]
Navigating up the content type hierarchy
Group by collapsed
Multiple group by
I think the docicon field is only available in classic
Adding a site field (perhaps my knowledge is outdated here)
Sums (Totals)
Calendar stuff for me. If I remember correctly (because I’ve moved to other stuff) surveys and task views are also better in classic
I don’t think I see “display the version number of a file.” Drives me nuts that I can add Promoted State to a view in “modern,” but not Version. I like to see both for the Site Pages library.
Other
Creating new Document Sets doesn’t work in modern interface.
Mapping a geolocation field in a list. Cannot be done in modern SPO
To deploy add-ins / command extensions to the app catalog.
Editing columns in a content type. SharePoint knows there’s a gap because it gives me a link to switch.
When we use SharePoint lists as data sources for Power BI, there are all sorts of tricks and gotchas. As with any rapidly evolving technology, some of the tricks we find on the InterWebs are helpful, some are out or date, and some are just plain wrong.
Recently I ran into a confusing issue when I was trying to use a Person or Group column in a Power BI report using the Power BI Desktop app. This could be an issue for any complex column from a SharePoint list that’s sparsely populated, though. Complex columns might include:
Person or Group
Manage Metadata
Lookup
Location
The reason they are complex is that they actually contain an object, not just a simple value like a Single line of text column, for example.
Expanding Complex Columns
After you’ve added a SharePoint list as a data source for your model in Power BI (doing this is well documented elsewhere), you generally will need to manipulate the resulting table so you can make use of all the columns, especially the complex columns. You can do this in the Power Query Editor.
Depending on which type of column you’re looking at, you may see List or Record in the Power Query Editor.
Person or Group columns will display as a List.
Managed Metadata columns will display as Record.
Notice in the screenshots above, you see the cool double arrow icon to the right of the column name. That means the column can be expanded to show properties of the underlying object.
Here’s what that process looks like for the Created By column. After I click on the double arrow icon, I have the choice to Expand to New Rows or Extract Values…. I choose Expand to New Rows .
Then I see Record instead of List. Why is that? Well, a Person or Group column is stored in an array when we get it through the connection – thus a List. First we flatten the array (there is only one value for Created By) and then we have what’s known as a Record.
When we click the double arrow again, we can choose which properties we want to extract from the Record. I’d recommend you only extract the values you actually want to use. In this case, it might be just be the title (which means the person’s name) for the Person or Group column.
Once we’ve made that selection, we end up with a new column (if you’ve taken the defaults), In this case, the new column is called CreatedBy.title. The great thing is it contains the thing we actually want to know: in this case, the person who created the item.
You can choose to rename the column if you’d like and do other things with it from here.
Sparsely Populated Issue
All the info above is just background. My issue was that I didn’t see the double arrows for some of the columns I knew were complex columns where I expected to see Record or List.
Here’s an example. Modified By looked the way I wanted it, but Referral Employee didn’t.
The Referral Employee was a Person or Group column, just like Modified By was. So why wasn’t I seeing the option to expand it?
I couldn’t figure out why this was, so of course, I turned to Twitter.
In #PowerBI, why do I see the option to expand some #SharePoint 'Person or Group' columns in the Power Query Editor and not others? None are multi-select.
The conversation was helpful, but it didn’t quite get me to a solution. Thanks to Treb Gatte (@tgatte) for jumping in to help. But it did give me some hints.
After some fiddling with the table, I tried to filter for only items which had a value in the Referral Employee column. (This was just by luck!) Once I did this, and I only saw items where there was a value in the Referral Employee column, the magical double arrow icon was there!
Fantastic! All in needed to do was follow the process I showed above for Created By, and I’d be all set!
Not so fast, smart guy. Once I did this and removed the filtering, I got this error.
OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table..
Because the Referral Employee column is sparsely populated, many of the values are actually empty. If you’ve been using computers for a while, you may know that empty, blank, null – and some other words – get tossed around all the time. They seem the same, but they really aren’t.
The error above was caused by the fact that the Power Query Editor was trying to expand empty values into records. I suppose it’s not smart enough to deal with that, but I can also see it as a good thing since you might want different things to happen when this is the case.
As you’ve undoubtedly seen, when you transform the table, the steps you take are piled up in the Query Settings on the right.
Underneath those steps is real DAX code [I was wrong about this: it’s M code. Thanks for the correction, Ingeborg!], and the fix for this issue requires some alteration of that code.
To get to the code view of the steps, go to View / Advanced Editor.
For the steps above, the code looks like this – but without line 7. Line 7 contains the fix. (There are several posts in the Resources below which gave me info on how to do this.)
There are a few things you need to know about this code to make the changes we need. Each transformation step relies on the step above it. You’ll notice that the Power Query Editor gives each step a unique name, adding numbers at the end if required. For example, Expanded Client and Expanded Client1 (the two steps to expand this Lookup column).
Essentially what we want to ask Power BI to do is transform the values in the Referred Employee column which are empty into nulls.
To do this, I inserted line 7:
#"Referral Employee Fixed" = Table.TransformColumns( #"Expanded Client1", {{"Referral Employee", each if Value.Is(_,type list) then _ else null}}),
Notice that the first parameter in the Table.TransformColumns function is the name of the prior step, which is #"Expanded Client1". The second parameter is the function which makes the replacement of empty values with nulls.
The other change we need to make is to edit line 8 to then refer to the #"Referral Employee Fixed" step rather than the #"Expanded Client1" step.
Somewhere between the classic Alerts functionality and Power Automate flows, we find the ability in Microsoft Lists to create rules for lists. Rules allow us to do some pretty basic – but VERY common things.
Creating a rule for a list is fairly straightforward. In the toolbar at the top of the list view, click on automate, and Create a rule.
There are only four options right now, but I expect this will expand. Microsoft is getting better at building what I think of as “bridging functionality”. Rather than the old days, where almost everything useful required a developer, there are now multiple options along a spectrum to accomplish many things.
The four options today are Notify someone when…
A column changes
A column value changes
A new item is created
An item is deleted
Note that #2 and #4 weren’t even possible not that long ago. We had to do all kinds of hocus-pocus for #2 and there simply wasn’t a “hook” for #4.
If you think about it, a huge number of SharePoint Designer workflows were built over the years JUST to send email notifications. This applies the 80/20 rule (one of my favorite rules – aka the Pareto Principle) to those use cases.
When you choose which rule you’d like to use – and I love how the graphics make it much easier to decide – you land on a screen where you just need to select a few values to set things up. If you’ve ever created a rule in Outlook to shuffle emails from specific sources into folders, you’ll recognize this type of logic.
The screen is also “list aware” – it knows which columns might make sense for each slot and what its possible values are. Here, I’m asking the list to notify me when the Category column is (equals) “(1) Category1”.
For the notification, all Person columns in the list are available.
As you can see, there’s little thinking involved: the columns are available in a dropdown when you need them.
Once you’ve set up a rule or rules, you can manage them from the same dropdown.
The ensuing screen shows you all the rules you have created, allowing you to delete them or just turn them off. The “Off” toggle is excellent to have when you’re loading content or making bulk metadata changes. You can stop the flood of notifications easily but equally easily turn the rule(s) back on.
Under the covers, this capability undoubtedly uses the web hooks for list items. This means the notifications will be fast and painless.
By the way, you may be wondering what those notifications look like. Truth is, they are nothing fancy. But they probably look better than 80% of the notifications you ever built in SharePoint Designer workflows over the years. This is another area where I expect the capability will expand, allowing some level of customization for the emails.
So the next time someone asks you to write a flow just to send an email when list items change, make sure to think about using rules instead. They will be amazed how fast you solve the problem.
Thanks to eagle-eyed reader Markus Bütterhoff’s (@buetti) comment on my post showing how to Group By Content Type in Modern Lists and Libraries, I learned of some recent changes for Content Types in Microsoft (SharePoint) Lists and Document Libraries. I’m not sure when these rolled out, but it must have been in the last few weeks.
As far as I can tell, all of these new capabilities work the same way in both lists and Document Libraries. I’ll say “list” below, but everything applies the same for Document Libraries.
After you’ve enabled Content Type management on the list, when you click on the Add column dropdown you’ll see Content type as an option.
This takes you to a funky little screen focused on Content Types. From what I can tell, if you’ve only enabled out of the box Content Types, it doesn’t do anything for you.
If you’ve defined any customer Content Types, then you can enable them on the list with this screen. If you’ve enabled a custom Content Type, you get some info about the Content Type and a button to remove it, but it’s not clear what else is supposed to happen here. My guess is this is the beginning of a replacement for some of the List settings classic pages.
If you click to the Show/hide columns option, you can add the Content Type to the view – painlessly. In the past, we’ve needed to navigate to the classic View settings page.
The best thing I’ve found so far, though, is what Markus pointed out to me in his comment: we can easily group by Content Type with no more query string tricks that sometimes fall down.
So easy!
It’s great to see Content Types back as first class citizens in SharePoint lists.
Connexion
