And SharePoint what I'll then do is show how to use odata to sort and filter Excel data and then we'll do the same thing with SharePoint the reason for doing them.
Differently is each one has its own issues and those issues are different for Excel and for SharePoint what I'll then do is show some Advanced filtering techniques we'll look at.
Dynamic filtering so you can use a variable to hold parameters and finally we'll look at experimental features which is a way to make it easier to write to odata filters.
And in this point you'll probably curse me and wish I'd shown you this at the beginning of the tutorial rather than at the end but that's enough of looking at me I.
So I've gone to odata.org it's the first thing which comes up when you search for a data and I presume the most important site and what this tells me is that odata.
Means open data protocol and it's basically a convention whereby you can get information from a website and apply filters and sorting I'm sure it does a great deal more than that but.
For our purposes that's what we're interested in as to why it matters have a look at this flow I've called it example of inefficient.
Flow and that's because it is it's based on a SharePoint list of 1200 films we'll come back to that in a bit and it doesn't apply any filters so if you look at the odata information.
The filter query here is empty and what that means is it will get back all 1200 films I didn't like this because I know it's going to go around very slowly on the.
- Working with Database using .NET (Access DB, OLEDB, C# .NET and VB .NET)
- How To Design, Fill & Send Unlimited Documents & Contracts In Excel Without 3rd Party Apps Part 1
- 460+ Professional Editable Logo Templates Download In PSD AI EPS Files English Photoshop Tutorial
Cloud - ID Card Make
Microsoft seemed to sulk when you apply when you send large requests through and the responses get slower and slower but Microsoft don't like it either you can.See there's a flow Checker here at the top with a red symbol next to it and if I click on that it's got a warning telling me that if I use get items without a no data filter I'll have slur.
Performance and if I ignore this and try running it the first time it gives me a warning too so that's what oh data is let's now have a look at the data sets we'll use for.
Our examples for SharePoint and Excel so here the examples we're going to use for this tutorial there's a file attached to this video called films with genres and you'll be.
Able to get that by looking at the program notes for the YouTube video and if they'll tell you which why is our web page you need to go to to download this file.
So that's in Excel and what you can do is bring that into both Excel online and also SharePoint a very quick reminder of how to do that for a SharePoint what you could do is choose to create a new list.
You could then choose to get the list from Excel choose to upload a file choose this file called films with genres it contains two tables make sure you choose the films table and.
You can click on next and proceed and it would create the list but I've already done that and it's called films on the left hand side if I click on that you'll see the contents of the list.
Aster Excel you can create a new folder possibly I created one called o data and within that you could choose to add a new file sorry uh I'll upload a file choose file.
Upload choose to get the file file fills
With genres and you can then upload that and if I now click on that you'll see that the contents of the work work complete with two worksheets films and.Genres that's what we're going to use so that's the data we're going to use for this tutorial what we'll now do is focus on Excel and then on SharePoint so to illustrate how you can sort and.
Filter data using odata with Excel let's create a new flow it's an instant Cloud flow what I'll do is call it odata and Excel manually trigger it.
And we'll add a step get the information from Excel and I'll choose list rows present in a table and as always go through the process of.
Choosing where I'm getting my information from OneDrive for business and then OneDrive choose my file which is in the odata folder.
And then choose my table which will be at the films table and then what I'll do is add a new Step beneath it to display the results of this using an HTML table.
And I will choose to display from the values from the list of data returned from my previous step choose custom columns and what I'm going to do is display the title The genre and.
The nominations you can actually just click in the header and then go into the next column to create rows but if you do this they don't actually have any headings so it's.
Probably a good idea to go back in and put the headings in so I'll call my film name nominations that's how many Oscars of film is nominated for and the genre.
And we're good to go so that's a fairly straightforward flow what I'm going to do now is show some things you can do without data and to be honest what I'm really doing is.
Highlighting of the various problems you'll have let's start with ordering and let's do some do something which I think probably will work.
We're going to sort them by a genre so that's that column there I can choose genre and I can type ascending ASC or descending I'm going to go for descending DSC and at this point.
If you program an SQL you'll have the illusory feeling that this is going to be just like SQL it isn't so now what I could do is save this and run it.
And when I run this what I'm expecting to see is all the films but I think it's westerns is the first genre in Reverse alphabetical order come first and then I'll work backwards through the.
Alphabet let's see if that actually works it's done and if I have a look at the output from that in my HTML table and you should see I've got excuse me.
Three columns but strangely I'm not seeing the results and I've had this before and I just wonder whether it's because I've got this file open.
It's suggested last time that wasn't the problem but let's actually try it again so what I'll do is try running this again I haven't made any changes whatsoever apart from.
Closing down that file and this time does it runs successfully it has so maybe that was a problem so you can see that I've got my westerns.
First before by my Warframe my Thriller it's all worked what I'd like to do now is to change this so I sorted by genre I'll change that to ascending and then within genre.
I'll sort it by the title this is valid uh oh data there's nothing wrong with it but I don't actually think it's going to work let's try it and see if my pessimism is Justified.
But you're running this uh and if I run my flow and choose done I was right my flow run has failed and the reason it's failed is because almost unbelievably only a.
Single expression is supported in the order by clause for Excel when we get onto SharePoint we'll see that's not true Excel seems to be far more restrictive about what you can actually.
Do with it as a data source so you simply can't do what I just tried to do so let's try something else I'll just go at editing that and what I want to do is to filter it I.
Was rather sorted by the runtime now the runtime has got a spacing so let's see if we can do that I'll delete my original order by I have not found any way to do this.
Whether in filtering or sorting single quotation marks like that didn't work square brackets like that don't work replacing the space with an underscore doesn't work I can't solve this problem.
It appears to be the case that the only way to solve it is to go into the Excel spreadsheet and change the heading to not have any spaces in they simply can't solve it so if anybody else can let me.
Know the answer so that's sorting let's have a look at some filtering so what I'm going to do is filter it to show all the films which got at least 10 Oscar nominations so I.
Could type in nominations is um let's say 10 more than 10. I can type nominations is greater than 10. and let's see if that works it's the first thing you would instinctively try.
Particularly if you come from any sort of computing background which I imagine most of you do does it work it looks it's looking good.
