Data For Knowledge channel on YouTube
I share data projects in Power BI and R Studio alongside technical insights to help you gain knowledge from your data.
So what does this involve? Well, using Power BI or R Studio I create follow along projects using open data, which is data anyone is free to use.
I explain some coding terms (mainly DAX or R), discuss data visualisations, and provide some technical videos relating to data analysis.
Please explore the videos in my channel. I hope you find them useful.
How to navigate Power BI tables and bar charts
Are you new to using Power BI? Here's a few tips on how to navigate bar charts and tables. To explore a Power BI report, it's all about clicking on diagrams and choosing the various options to view the data in different ways.
Power BI Waterfall Month by Month example
Using Power BI’s Waterfall chart, finance data is plotted to show a month by month movement in income and in costs with the ability to look at the monthly trend for each cost. I bring in a small dataset, then perform a small amount of manipulation to the data before displaying the movement in Waterfall charts..
Power BI – Dynamic titles – Date, single selection, multi-selection
In Power BI, three examples of dynamically changing a page or chart header/title. Examples: Today’s date is added to a page; The page title updates based upon the single selection from a slicer; A chart title updates based multi-selection from a slicer.
Power BI – Plot cultural places on a map using an API and Open Data – Easy report to create
Using Open Data this project shows how easy it can be to pull data into Power BI using an API and displays the results on a map. A user then has the ability to filter the map and source a link to websites of interest.
Power BI – Comma separate numeric value in a text field
In Power BI you have text as the title to a page or diagram and within that text is a numeric value. You would like the value to be comma separated. This video shows you how easy this can be to fix.
Power BI – Card visual – (Blank) to Zero – Implicit to Explicit measure
In Power BI, change from (Blank) to the number zero in a card visual by using a measure.
In R dataframe – how to alter the number of observations and/or variables
In R dataframe – how to alter the number of observations and/or variables.
Power BI – Slicers – Filter table by rows and columns- uses New parameter Fields option
In Power BI, an example of how to use slicers to filter a table by rows and uses the ‘New parameter – Fields’ option to create a slicer to filter by columns (from time 3.06). This allows a table to be filtered by both rows and columns.
Power BI – DAX INT and TRUNC expressions for rounding figures
DAX expressions – INT and TRUNC. Walkthrough discussion of the results of applying INT or TRUNC to round figures in a table in Power BI. For TRUNC it includes consideration of positive and negative figures appearing in the num_digits parameter.
In RShiny create data table with buttons using open data from a geoJSON file – follow along
Create a data table with buttons in RShiny. The buttons allow the user to download or copy the contents of the table. The data is sourced from a geoJSON file.
Power BI – Inbuilt page navigation – change state from default, hover, press, selected
Power BI has an inbuilt page navigation option. Cosmetic changes are made to the navigation buttons and a shape is inserted behind the pane to make buttons look like they are joined together.
Use colours in diagrams to help the user, not confuse!
When selecting colours for a diagram, it is important that the colours used will both make sense to the end user and accurately display the information the data is conveying.
R Project – how to create bar chart (ggplot2) from spreadsheet-includes data pivot & remove a column
Follow along practical example – In an R script upload a spreadsheet, manipulate the data by removing column/variable, change variable name, and pivot columns, and then producing a bar chart..
With the sort order of categories in diagrams – have you used the order expected by the user?
When adding categories to a chart, think about whether the user will read all the category names. Consider, is there a risk the user will see the start of a pattern and unconsciously fill in the rest.
Create a Wikidata Query – example using Shipwrecks data
A follow along example of creating a query in Wikidata. Here, the data of where ships are considered to have been wrecked off the coast of Scotland, UK has been used. Wikidata query created in the video can be found at here.
Bring Wikidata into Power BI using a simple R script – example
A follow along example – Coordinates from Wikidata are brought into Power BI for plotting on a map using a simple R script. The data used in this example is where ships are considered to have been wrecked off the coast of Scotland.
SWITCH() to change the background colour in a Power BI table
Using a DAX SWITCH function to change the background colour of a cell in a Power BI table. In this video I provide an example of the cell colour changing based upon the number of dates to a project deadline.
Power BI – Append – walk through example using Open Data
Join two different datasets with similar type of content using Append in Power BI. Here, two open data datasets relating to places in Dundee, Scotland, UK have had their rows combined together into one table using Append.
Power BI – Add symbols and emojis
For Power BI a follow along example explaining how symbols and emojis can be added to a report. After a short demonstration adding
symbols, attention is turned to creating a medals table based upon the ranking of profits.
Power BI – Create Treemap using Open Data – Easy follow along project
In Power BI, an open dataset it used to create a Treemap and Slicers. A small amount of data tidying and manipulation is undertaken to allow the treemap visual to be created.
Power BI – How to navigate line diagrams (line charts) – Introduction
Are you new to using Power BI reports? Here’s a few tips on how to navigate line diagrams (line charts).
In R Shiny create leaflet map, add layer of rectangle shapes, toggle layer on and off – example
n this R Shiny example I create a leaflet map, add rectangle shapes to indicate harbour boundary estimates, add shape to a layer, and provide the facility to toggle the layer on and off.
Using OpenStreetMap osmosis extract street names from polygon on map for display in RShiny table
Osmosis tool from OpenStreetMap (OSM) is used to extract the name of streets within a catchment polygon shaped area on a map. Uses .poly, .osm, and .pbf files. Street names gathered for an ongoing project in an RShiny app.
Power BI – DAX ROUND ROUNDUP ROUNDDOWN MROUND expressions for rounding figures
Walkthrough discussion of the results of applying ROUND, ROUNDUP, ROUNDDOWN or MROUND to round figures in a table in Power BI.
Power BI – Column headers of a table – Minor changes to the design can help the user
In Power BI small changes are applied to the column headers of a table These small changes can make a difference to the report’s aesthetics and the user’s understanding of the visual.
CRUD – User requirements for data record in a database
Create, Read, Update and Delete a record in a database- in a lot of apps that is all users are looking to be able to do.
Why add labels to charts? What labels can be helpful?
Chart labels and descriptions help provide context to the data and can aid to reduce misunderstandings of the information they contain.
Power BI – Gantt chart showing asset hire periods. How to create off-hire periods in gantt chart.
A customed gantt chart shows the dates assets are on hire. The chart is then updated to show the gap periods when the assets are available for hire. The off-hire periods are not in the dataset therefore they are created using a merge, with the available periods then an appended to the original dataset.
Map Wikidata in an R Shiny App – example
A follow along example to map wikidata in an R shiny app. The data relates to where ships are considered to have been wrecked off the coast of Scotland. The map is OpenStreetMap using a leaflet package.
Power BI hyperlinks – link to webpage URL – chain and underline methods
Two methods of creating a link from contents on Power BI to a webpage (URL). The first creates a chain link symbol for redirect to a webpage/URL, the second creates an underline/underscore to a word or phrase to act as a redirect to the webpage/URL.
Insert Wikipedia extracts into a Power BI report using an easy API request- example
A simple Wikimedia API request is used to pull Wikipedia extracts into a Power BI report. In this follow along example, a slicer is created to hold the name of ships, an image of the relevant ship is pulled in from Wikimedia Commons, and an extract relating to the ship is pulled in from Wikipedia.
Map direction of ship travel using AIS data in R Shiny app on a Leaflet map – follow along example
Automatic Identification System (AIS) open data is used to plot the direction of travel of ships on a map provided by leaflet inside an R shiny app. The data has kindly been openly provided by the Norwegian Coastal Administration.
Power BI – DAX function RANKX – Dense and Skip
Using the DAX function RANKX in Power BI, this video demonstrates the treatment of the next item in a rank after a tie – would you like the next rank position to be the continuous or would you prefer to skip that position and go to the next.
Using R code, merge 3 csv files using left joins in an R Script – follow along using open data
Using R code, take 3 separate csv files and merge them together using a left join to provide more user friendly information for an end user. Designed as a follow along video, using third party open data we bring in details of births in Scotland and add to that information the names of the hospitals where the births occurred along with the council areas in which the hospitals reside.
Power BI – Small Multiples Diagram (Chart) example
In Power BI a line diagram (line chart) is created, and then using a feature called Small Multiples the diagram is altered to contain many mini line diagrams before being filtered to make it more readable.
RShiny project – Plot polygons from GeoJSON file to leaflet map and plot circles from excel file
In this RShiny follow along project, a geoJSON file contains coordinates which will be used to create polygons (shapes) on a leaflet map indicating school catchment areas. Onto this, circles are added showing the location of the schools within the catchment area.
Power BI – DAX CEILING and FLOOR expressions for rounding figures
DAX expressions – CEILING and FLOOR – rounding figures. Walkthrough discussion of the results of applying CEILING or FLOOR to round figures in a table in Power BI. For FLOOR it includes consideration of positive and negative figures appearing in the significance parameter.
Power BI project – Create image grids – Images from Wikidata brought in using a simple R script
A follow along example using images from Wikidata in Power BI. The data is inserted into Power BI using an easy R script with the images then displayed in diagrams.
When a left join of data tables goes wrong – accidentally omitting or creating data – oops!
Please be careful when merging tables to make sure that no data inadvertently disappears or you inadvertently create data.
R Project – create line chart with points (ggplot2 & plotly) from spreadsheet-includes data pivot
Follow along practical example – In an R script upload a spreadsheet, manipulate the data by pivoting columns, and then producing a line chart with data points (dots).
R programming – how to create small multiples diagram using facet_wrap and facet_grid from ggplot2
Using facet_wrap and facet_grid functions of ggplot2 to create small multiples diagrams from an initial line diagram.
R programming – ggplot2 legend- examples of how to add, remove, alter the legend
R programming – how to add, remove, and perform some alterations to the legend of a ggplot2 diagram using theme(), and guide() with guide_legend() .