R-scripting and PowerBi (2)

For my Data Science Course we are using R (and R-studio) to make our assignments.

As I’ve writing earlier the new version of PowerBi has R-capabilities in the desktop version. So it was interesting to see if I could also perform the exercises in PowerBi. And for this weeks assignment I tried it (and succeeded).

The data is on a Website in zipped format. By using R-script to read the data it is possible to get the dataset into PowerBi. In R-studio we had to subset the data for making the plots (this weeks assignments are about visualization of Data). The assignment was to use only the dates 2007-02-01/02. But in PowerBi I solved that in the Dashboard and made it more interactive.

We had to make 4 different Plots in this weeks assignment and upload each individually to GitHub. In PowerBi I  created all 4 plots in one Dashboard. See the feature image for the results.

I’m pleased with the results. In PowerBi the interactivity and Dashboard approach is far more flexible. Up t0 the next assignment to see if that is also possible in PowerBi.

Here are some of the coding I used:

Getting the Data with R-Scripting

let
    Source = R.Execute("subDir <- ""data""
      if (file.exists(subDir)){     # all done
       } else {
       dir.create(file.path(""."", subDir))
       }
    fileUrl <- ""https://d396qusza40orc.cloudfront.net/exdata%2Fdata%2Fhousehold_power_consumption.zip"" 
   download.file(fileUrl, destfile=""data/household_power_consumption.zip"") 
   unzip(""data/household_power_consumption.zip"", overwrite = TRUE, exdir=""data"")
   household_energy <- read.csv(""data/household_power_consumption.txt"", header = TRUE, sep = "";"")
   working_data <- household_energy#"),
    working_data1 = Source{[Name="working_data"]}[Value],
    #"Changed Type" = Table.TransformColumnTypes(working_data1,{{"Date", type text}, {"Time", type text}, {"Global_active_power", type text}, {"Global_reactive_power", type text}, {"Voltage", type text}, {"Global_intensity", type text}, {"Sub_metering_1", type text}, {"Sub_metering_2", type text}, {"Sub_metering_3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateTime", each [Date] & " " & [Time]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateTime", type datetime}})
in
    #"Changed Type1"

 Making a Graph using R in PowerBi

Only the part is including that is not auto-generated. This is the code for the upperleft graph

hist(as.numeric(as.character(dataset$Global_active_power)), main = "Global Active Power", col="red", xlab="Global Active Power (kilowatts)")

The code for the left-under graph:

dataset$Sub_metering_1 <- as.numeric(as.character(dataset$Sub_metering_1))
dataset$Sub_metering_2 <- as.numeric(as.character(dataset$Sub_metering_2))
dataset$Sub_metering_3 <- as.numeric(as.character(dataset$Sub_metering_3))
dataset$dateTime = strptime(paste(dataset$Date, dataset$Time), "%d/%m/%Y %H:%M:%S")

plot(dataset$dateTime, dataset$Sub_metering_1 , type="l", col = "black", xlab ="", ylab = "Energy sub metering")
lines(dataset$dateTime, dataset$Sub_metering_2 , type="l", col = "red")
lines(dataset$dateTime, dataset$Sub_metering_3 , type="l", col = "blue")
legend("topright", lty=1, col = c("black", "red", "blue"), legend = c("Sub_metering_1", "Sub_metering_2", "Sub_metering_3" ))

The upper-right graph:

dataset$Global_active_power <- as.numeric(as.character(dataset$Global_active_power))
dataset$dateTime = strptime(paste(dataset$Date, dataset$Time), "%d/%m/%Y %H:%M:%S")
plot(dataset$dateTime, dataset$Global_active_power , type= "l", xlab="", ylab="Global Active Power (kilowatts)")

And the last-one (lower-right):

dataset$Sub_metering_1 <- as.numeric(as.character(dataset$Sub_metering_1))
dataset$Sub_metering_2 <- as.numeric(as.character(dataset$Sub_metering_2))
dataset$Sub_metering_3 <- as.numeric(as.character(dataset$Sub_metering_3))
dataset$Voltage <- as.numeric(as.character(dataset$Voltage))
dataset$Global_active_power <- as.numeric(as.character(dataset$Global_active_power))
dataset$Global_reactive_power <- as.numeric(as.character(dataset$Global_reactive_power))
dataset$dateTime = strptime(paste(dataset$Date, dataset$Time), "%d/%m/%Y %H:%M:%S")

par(mfrow = c(2, 2))
plot(dataset$dateTime, dataset$Global_active_power, type="l" , col= "black", xlab = "", ylab = "Global active power")
plot(dataset$dateTime, dataset$Voltage, type="l" , col= "black", xlab = "datetime", ylab = "Voltage")
plot(dataset$dateTime, dataset$Sub_metering_1 , type="l", col = "black", xlab ="", ylab = "Energy sub metering")
lines(dataset$dateTime, dataset$Sub_metering_2 , type="l", col = "red")
lines(dataset$dateTime, dataset$Sub_metering_3 , type="l", col = "blue")
legend("topright", lty=1, col = c("black", "red", "blue"), legend = c("Sub_metering_1", "Sub_metering_2", "Sub_metering_3" ))
plot(dataset$dateTime, dataset$Global_reactive_power, type="l" , col= "black", xlab = "datetime", ylab = "Global_reactive_power")

Likes(0)Dislikes(0)