LG Inform Plus and Power BI training videos

Hello.

In this video, we're going to show how to import data from the LG Informe Plus API into Power BI. So here I am in LG Inform Plus.

And as in previous videos, I'm in the data tool and I'm logged in. So you see, I got my account information there, and I've just conducted a really simple query. My query is that I'm interested in the total population metric type for the wards of Birmingham and I'm looking at the latest three periods, so the last three periods for which that data is available.

And so over on the right hand side here we have our preview of the data so we can see we've got our list of wards and we've got the last three years for which that data is available and the total population for each. Now a great way to get data into Power BI is to use the JSON table export format. It makes the process of importing that really simple.

So that's what I'm going to show you today. So up on the top right here, just above our data, you can see we've got a range of different options there. We can download a CSV, for example. And there we've got these two different JSON options: we've got view the JSON table, and we've got view the JSON and the one that we want is to view the JSON table. It's just a slightly more flattened view than a standard JSON view of it.

And this makes the import process into power by really nice and simple. So I'm just going to click on that and you can see in the browser, which just loaded up the data, and it's given me in the address bar here, this is the link to this set of data in this JSON format. And the way that JSON is structured in this example is each of these blocks that lives within the curly braces - you can see we've got this top one here. there's a second one here.

Each one of those is a particular ward in a particular year and the population for that. So this first one here we look down the list of attributes for that particular block. We've got the area label that's Acocks Green. So that's the ward. We've got the period label 2018. So that's for the year 2018 and the value 24,279.

So that's the total population for that ward in that year. So each one of these different blocks is a particular ward of Birmingham in a particular year. So to get the data into Power BI the first thing we're going to do is copy the URL. Now it's really important to make sure that you get all of the URL. So we've got you can see here this little question mark in the URL and a bunch of parameters after it.

We want to make sure that we grab all of those. So if it's very long on your browser, click Ctrl + A on your keyboard before you copy, and that will make sure that you've grabbed everything. Otherwise it won't work. So I've done that. I'm going to hit Ctrl + C to copy that to my clipboard, and now I'm going to head into Power BI. And there's a couple of different ways in which I can get the data in from my JSON link. I can go to get data and click on the down arrow there and you can see that there's an option there for Web.

So that's the correct option. But you can also, if you're on this entrance page here you can click, get data from another source, and it will offer me some options in a moment. I need to go to other and then go to Web in there, so they're going to take you to the same place in the application. But there's a couple of different places where you can get to that. So I'm just going to click Connect there, and what that's inviting me to do is enter a URL, so I'm just going to paste in the URL. But I've just copied and I'm going to click.

OK, and what that will do in a moment is just pull all of the data in from my JSON URL. So there we go. There's my data. You may if you've imported data previously, get this notice just to say it wonders if the data is fully up to date. It's fine to click refresh if you want to do that, just to make sure that you've got the most recent version of the data. And what Power BI has done is it's taken each of those blocks that we saw in the JSON preview in the browser, and it's turned each one of those into a row.

So exactly as we were looking at the example here, this first row we can see is Acocks Green and it's in 2018, and if we go over here, we can see the value there. So all it's done is it's parsed and converted that data into a format that it can then use for us to build reports and charts and things like that. So we're going to cover those off in the following videos. That's all for now!

Hello.

In this video, we're going to show how to create a simple chart in Power BI using data from the LG Inform Plus API. Now, this video follows on from a previous one in which we talk about the process for getting the data out of the API and into Power BI. So if you haven't seen that one already, go and take a look at it and then come back here.

So here I am in Power BI and I've just imported my data using the same steps as shown in the previous video. And I'm actually using the same example data as was shown there as well. So we're showing the wards of Birmingham and the total population for each of the last three years that are available. So when we import our data, the first thing that we see is this summary table, and this is our opportunity to just check that everything seems to have imported correctly, and it is as we would expect it to be.

So each of these rows is an individual record, and in our example a record is a combination of a ward and a year, and then we show the population for that combination. And each of our columns is a particular data point or a field that's available for each record. So if I just scroll over here, we can see that there's quite a few columns and it's this one here, this value column that contains the number with the population in it.

Now our example is going to be a very simple chart. All I'm going to do is plot out the the different wards and across the different years and show the population for each of those. So for that we don't need all of the different columns that we're showing here. So the first thing we're going to do is reduce the columns to just those ones that we need. And this is generally a good practice to keep your reporting manageable if you know exactly what it is that you're going to be building.

So I'm going to un select this checkbox for 'select all columns', and now I can just pick the ones that I'm interested in. And those are the area label: that's the name of the ward. The period label, which is the name of the year; and the value, which is the population number for that area in that year.

So if I click OK, we can see that Power BI has just stripped our table down to just those columns that we've asked for. So I give that a quick check over and I'm quite happy with what I'm seeing there. I'm going to click close and apply, and that will take me into the reporting interface and allow me to start to build my chart. You can see that it takes a few moments to import all of the data that we need. And when we finished, it's ready to build our visuals and we can see over in the right hand side here, this fields area.

If I just expand that data table icon, we can see the columns that we've got available to us. And those are those three that we've just selected. Here are my different chart types, and I want a simple clustered bar chart. So I'm going to click on that and that will put one in the canvas for me. And you can see it just gives me a little placeholder so I can see more or less what that's going to look like and how big it's going to be, and so on.

So as it says here, I need to select or drag fields to populate the visual. What we're going to do is take our data points over here. Are we going to drop them into the various different areas here, and depending on how we do that, that will dictate how that information is represented in the chart. So the first thing I'm going to do is put our value column into our value area here because I know that the the population number is the value that I'm representing on the chart. And you see, at the moment it doesn't really tell us very much because I haven't given it any way to break that information down.

So all it's doing right now is summing the total of all of those different values for all of the different rows. Of course we want to break that down according to the wards. So we're going to take our area label column, and we're going to pop that in the Axis area here, and you can see suddenly this starts to look much more like a meaningful chart. We've got our wards and we've got the population for each of them and Power BI has ordered that according to the size of the population, but actually we still haven't broken down by year.

So what this is really showing is a some of those three years for each of those different wards, which is not what we want. So the final thing we need to do is apply the year onto the report so that we can see the breakdown that way. There's two different ways that we can do that. We can either do it using the small multiples feature, and you can see what that does is it gives us a separate small charts, which we can compare by scrolling.

But actually that's not how I want to represent the data this time around. What I want to do is have a single chart, which has the breakdowns for all of the wards within it. So I'm going to move the year into the legend area here, and what that gives us now is a single chart that contains all of the wards, and it contains the three different years of population for each of those wards. And you can see it's also given us a legend so we can see which colour corresponds to which year. So I'm quite happy with that.

That's showing exactly how I wanted to show its ordered by default by by size. And there's some filtering options. And by default Power BI I will allow The user is viewing this report to filter based on, for example, the name of the ward or the year. Now the final thing I probably want to do is improve some of this labelling and terminology here because these are just the name of the fields as they've come from the data source. We want to make these really represent what it is we're looking at on the charts. So I'm just going to go over here and double click on the name of the column and area label I want to change to ward, period label I want to change to year, and value I want to change to total population

And now you can see when we look back over here, we've got much more meaningful labelling, so to somebody coming along and having a look at this chart, they know exactly what it is that they're seeing. So that's it for this example. That's just a very simple one. We will do some more advanced use cases in later videos, but for now, that's it.

Hello.

In this video, I'm going to show you how to merge queries and create a drill down charts in Power BI

using data from the LG Inform Plus API. Now there's a couple of videos before this one that covers the basics of importing your data to Power BI and creating a basic chart with that. So if you're unsure on any of those basic items, have a look at those videos first and then come back to this one.

So here I am in Power BI I and all I've done is I've imported some data from a JSON URL that I've generated on the LG Inform Plus website, and it's the same data as I've used in the previous example videos. So we've got the wards of Birmingham across the last three years, and we've got the total population for those wards in those years.

And what I've done here is I've used the choose columns tool to make sure I'm only looking at the columns that I need for my particular chart. Now, in this video, I want to make more advanced charts in Power BI, in which I include some additional information that doesn't exist in this data source so that I can drill down from a wider area, type into the ward.

And in my case, I'm going to use parliamentary constituencies as as my wider area. So to do that, I've got to bring that information into my data source here. And I'm going to do that by importing an Excel spreadsheet that I've created separately.

So I just put this together manually by looking up a list of the parliamentary constituencies and the wards that make up those constituencies in Birmingham. So in Power BI what I'm going to do is add a new source to this data source. So we'll have two data sources. But before I do that, what I want to do is just rename this one to 'ward data'. And I'm just doing that to make sure that I'm clear when I'm configuring my options that I know exactly which data source I'm configuring when I'm doing that.

And now we can add a new data source. And in our instance, we're going to use the Excel workbook that I've just shown you. But you can add additional data sources of any of these other types, and that might include further data sources from the LG Inform Plus API. So I'm going to click Excel Workbook and then select the workbook that I just showed you.

And then what that will do is ask me to select my tab. There's anyone tab, and then I'm going to hit OK. So there we go. I've now got two data sources for my report, and I'm just going to relabel this second one as 'parliamentary constituency data', again, just so I know what it is I'm working with.

My objective here is to take this data from column two and add it as a column in this data source. So I've got a single table that contains my ward, my constituency, the time period, and then the population, and that will allow me to do the drill down report when I get to building that.

What I'm going to do is use the merge query tool, and this will allow me to join together the two data sources. So, this one here is the one that I've already selected. And this is the one that I want to add the data to and I'm just going to select 'PC data' as my second data source, and I'm going to select a full outer join.

For most intents and purposes, these join types don't make too much difference. I like to use a full outer join because it's very complete. If you're not sure what type to use, then just have a play around and see what results you get from that. I'm not interested in using fuzzy matching because I'm confident that the spelling of all of these wards in my second data source are exactly the same as the spellings of the wards in the first data source.

So there should be no need for fuzzy matching there. And so finally, what I'm going to do is select the column on which my matching is going to occur. And in our instance, the common column across the two of them is the name of the ward. So these two columns here and then I hit OK, and you can see that something's happened. We've pulled some data into our first data source, but it's not in a format that we can use as it stands currently.

What it's actually done is it's created a mini table that it's set into each of these rows, and that's not really what we want for building our reports. So I'm going to click on this icon here, and what this will allow us to do is directly embed the data from those two columns into this table here. Now, if you remember, that other data source had two columns, it had the ward name and the constituency name.

Now we don't really need the ward name again because we've already got that, so I'm going to uncheck that, and I'm just going to tick the parliamentary constituency box there. Now we'll still get the right constituency against the correct ward because although that column is not being added, it's still being used to match the data together, so we don't need to worry about showing it. It's still going to be used for matching. So we hit OK, and there we go.

We've got our constituencies - sorry, we've got our wards, we've got our constituencies, got our years, and we've got our populations. So that's everything we need. Now let's hit close and apply, and that will move us into the report builder. It'll just take a moment to load in the data that we need.

There we go. So we're really just interested in that ward data table because we've now got our four columns. We've got our wards, constituencies, years and populations. We're going to start in much the same way as in the previous video by creating a clustered bar chart. I'm just going to click the icon. Here's our clustered bar chart placeholder.

And I'm just going to start by adding some of these fields into these areas here and, again, much the same as the previous videos. So our value field is going to go in our value column. That's our number. Our area label field is going to go into our axis, and our period label field is going to go into our legend.

So this is pretty much the exact same report as we had previously. And what I'm going to do is just really quickly give these human readable names so they make sense. There we go. Now, what I want to do is actually create a chart which shows firstly the constituencies and then allows me to drill down from those constituencies to the wards and the way we're going to do that is to create a hierarchy within this axis area here.

So we're going to take our parliamentary constituency field. I'm going to drop it into the same area, and you might just be able to see that yellow line, that's determining whether I put it above or below the ward in the hierarchy. And I want it above it because I wanted to be the first thing that the user sees when they access this report. So I've pop that in there. I'm just going to type in constituency, so we've got a good label. And you can see here now, we've got a list of all the constituencies, and what that's doing is it's now summing up the populations from all of the wards that belong within that constituency across those three different years.

So we can see there. That Ladywood, for example, has got the largest overall population out of all of those constituencies. Now, if we want to drill down, so we want to go and have a more detailed look at Birmingham Ladywood and see how those wards compare to one another, I can just right click on that bar, I can click on Drill Down and what that's now done is loaded up all of the wards that I have categorised as being within the Birmingham Ladywood constituency, and it's got their populations plotted against them across those three years.

And if I want to go back up to the top, I just click the up arrow, and that takes me back up into my list of constituencies again. So drill down reports a really great way of exploring data that has a sort of hierarchical structure and categorisation that might be across a couple of different axes. You can give your users of the reports a chance to kind of explore that data without bombarding them with too much information within the same interface. So they're really useful for that.

And when you combine data sources, you can really developed quite a lot of richness in your reports So I hope that was useful. Thanks very much.