Integration of Anaconda and PowerBI Desktop

While integrating Anconda and PowerBI the error people at first attempt encounter is:

DataSource.Error: ADO.NET: Python script error.
Traceback (most recent call last):
  File "PythonScriptWrapper.PY", line 2, in <module>
    import os, pandas, matplotlib.pyplot
  File "C:\Users\mm\AppData\Local\Continuum\anaconda3\lib\site-packages\pandas\__init__.py", line 19, in <module>
    "Missing required dependencies {0}".format(missing_dependencies))
ImportError: Missing required dependencies ['numpy']

Details:
    DataSourceKind=Python
	
... 


The issue you are facing is of the Power BI and Anaconda integration. You have to follow the below steps that link Anaconda with the Power BI.

Check that PowerBI is configured to use Python.
Go to "File" >> "Options and Settings" >> "Options" >> "Python Scripting"




Open the Anaconda Prompt:
Then, you have to go to the Conda Environment that you want to use in PowerBI. Am having an environment 'temp', so I activate it first in the 'Anaconda Prompt':

(base) C:\Users\ashish>conda activate temp

Then I go to the directory having the "PowerBI" executable file in the installation folder:

(temp) C:\Users\ashish>cd "C:\Program Files\Microsoft Power BI Desktop\bin"

Then, I launch PowerBI from the Prompt:

(temp) C:\Program Files\Microsoft Power BI Desktop\bin>PBIDesktop.exe

This fixes the NumPy error you are getting. If you want any other package to use with PowerBI, install that package in the respective "Conda Environment" (in my case it is "temp").

Make sure the Python home directory (Anaconda3) has been added to the 'Power BI Desktop' global options in the Python scripting section too.

Timeline View Using HTML Content Visual in PowerBI

We are creating an HTML based 'Timeline' visual in PowerBI.
We have a dataset in Excel file that looks like this:




We are going to use Python to add clock icons according to time recorded in "dt_ts" column.

Go to "Home >> Transform Data >> Transform >> Run Python Script".
Ignore any Privacy warnings that you might get while running script the first time.

Code for the "Python Editor":

# 'dataset' holds the input data for this script
from dateutil.parser import parse

def get_icon(x):
 icon_dict = {
  "1:0" : "🕐",
  "2:0" : "🕑",
  "3:0" : "🕒",
  "4:0" : "🕓",
  "5:0" : "🕔",
  "6:0" : "🕕",
  "7:0" : "🕕",
  "8:0" : "🕗",
  "9:0" : "🕘",
  "10:0" : "🕙",
  "11:0" : "🕚",
  "12:0" : "🕛",
  
  "1:30" : "🕜",
  "2:30" : "🕝",
  "3:30" : "🕞",
  "4:30" : "🕟",
  "5:30" : "🕠",
  "6:30" : "🕡",
  "7:30" : "🕢",
  "8:30" : "🕣",
  "9:30" : "🕤",
  "10:30" : "🕥",
  "11:30" : "🕦",
  "12:30" : "🕧",
 }
 hour = parse(x['dt_ts']).hour
 min = parse(x['dt_ts']).minute
 if min >= 0 and min < 30:
  min = 0
 else:
  min = 30
 return icon_dict[str(hour) + ":" + str(min)]
 
dataset['icon'] = dataset.apply(get_icon, axis = 1) 

The above code creates a new column "icon".

Then, "Close and Apply" 'Transform Window' and come back to "Home" tab.
There on the right hand side, you can see your 'Sheet1' (Sheet1 in our case, this is the name of your Excel sheet or Database table).
Click on "Ellipsis" and "New Column".




What we are going to write next is a mix of HTML, CSS and DAX code.

HTML = "<p style='font-size: 25px'>" & Sheet1[icon] & ": Message: " & Sheet1[read_time] & "; Char: " & IF( 
    OR(ISBLANK(Sheet1[col_with_empty_cells]), LEN(Sheet1[col_with_empty_cells]) = 0) , "NA", Sheet1[col_with_empty_cells] )  & "</p>"

To get the HTML Visual, we get the HTML Visual from GitHub

Load the Visual using the downloaded "pbiviz" file. (Ref: PowerBI's HTML Content Visualization)

To get the HTML content in the Visual:
Either: Drag the column into the 'HTML Content' Visual.
Or: Click on the 'Tick' mark before the "HTML" column in the side bar on the right named "Fields".

Output:




References  

% GitHub Link to PowerBI Notebook and Excel used for this demo
% Intergration of Anaconda and PowerBI
% Get 'HTML Content Visual' from store.office.com
% OR Function DAX (Microsoft)
% DAX Operator Reference (Microsoft)
% Clock Symbols

Concatenate Two Tables using R in PowerBI

We have two Excel files. 
x1.xlsx has sheet "EngSheet".
x2.xlsx has sheet "HindiSheet".

(1) Load the data. For that we use "Get Data" functionality of PowerBI.

(2) We will concatenate the two sheets vertically.

For that, we go to "Transform" tab of "Dashboard" (this opens "Power Query Editor") and then "Transform" tab of "Power Query Editor".

(3) We select "EngSheet" from the given "Queries" column.

Then, we click on "Run R Script", provided we have R installed on our system and PowerBI is configured to use it.

This opens the editor, but we do not write any code in this step and "CLICK OK" to close it.

(4) Ignore all "Privacy" warnings related to running scripts in the prompts that appear after step (3).




(5) We go to "View" tab in "Power Query Editor" and enable "Formula Bar".

The "Formula Bar" has some code like this:

= R.Execute("# 'dataset' holds the input data for this script",[dataset=#"Changed Type"]) 




(6) We modify this line in "Formula Bar" to pass our two datasets as the input to the R script.

For that the code is:

= R.Execute("# 'dataset' holds the input data for this script",[dataset=EngSheet, dataset2=HindiSheet]) 

Note: The output of this activity will go into the "EngSheet" (denoted by "dataset" above) overwriting its contents.

If you have selected "HindiSheet" from "Queries" column in step (3), and in you are passing it as one of the table in arguments as in: dataset=EngSheet, dataset2=HindiSheet

You will encounter error: Expression.Error: A cyclic reference was encountered during evaluation.

(7) To resolve this we create a third "XLSX" file (xl3.xlsx) with a sheet named "Sheet1" and sheet has a header with "col1" and "col2" written in the first two cells of the first row. 

(8) Load xl3.xlsx in PowerBI workspace.

(9) We go to "Transform" tab of "Dashboard" (this opens "Power Query Editor") and then "Transform" tab of "Power Query Editor". Now, we select "Sheet1" from the given "Queries" column. "Queries" column shows all the three sheets we have loaded in the PowerBI workspace.

(10) We click on "Run R Script" and "CLICK OK" to close the "Script Editor".

(11) In the formula bar, enter:

= R.Execute("# 'dataset' holds the input data for this script",[dataset=EngSheet, dataset2=HindiSheet])

(12) We need an R package, "sqldf" for this activity. If you don't have it, install it first from "RGui":

> library(sqldf)
Error in library(sqldf) : there is no package called ‘sqldf’
> install.packages('sqldf')
Warning in install.packages("sqldf") :
  'lib = "C:/Program Files/R/R-4.0.3/library"' is not writable
Installing it in "Temp" or "Local Documents".
--- Please select a CRAN mirror for use in this session ---
also installing the dependencies ‘ellipsis’, ‘glue’, ‘bit’, ‘rlang’, ‘vctrs’, ‘digest’, ‘bit64’, ‘blob’, ‘memoise’, ‘pkgconfig’, ‘Rcpp’, ‘BH’, ‘plogr’, ‘gsubfn’, ‘proto’, ‘RSQLite’, ‘DBI’, ‘chron’

trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.0/ellipsis_0.3.1.zip'
Content type 'application/zip' length 46014 bytes (44 KB)
downloaded 44 KB
...
...
etc., etc.

(13) In the "Power Query Editor", Click "Run R Script".

(14) Enter the following R code:

# 'dataset' holds the input data for this script
library(sqldf)
m <- sqldf("select Name as AllNames, Personal_Number as AllNums from dataset
union all 
select Naam as AllNames, Ank as AllNums from dataset2") 

Click "OK". 
If you encounter an error such as "Error: no such table: dataset2", make sure you have a code snippet like the one below in the "Formula Bar":




= R.Execute("# 'dataset' holds the input data for this script#(lf)library(sqldf)#(lf)m <- sqldf(""select Name as AllNames, Personal_Number as AllNums from dataset#(lf)union all #(lf)select Naam as AllNames, Ank as AllNums from dataset2"")",[dataset=EngSheet, dataset2=HindiSheet]) 




If you have a Space in a column (if "Personal_Number" is present as "Personal Number"), you get following error for code:

# 'dataset' holds the input data for this script
library(sqldf)
m <- sqldf("select Name as AllNames, 'Personal Number' as AllNums from dataset
union all 
select Naam as AllNames, Ank as AllNums from dataset2") 




(15) "Close And Apply" the "Power Query Editor".

(16) "Table Visual" of Sheet1:




Additional Note
1: If you want to format a 'Date' type column in R:

  dataset$derived_date <- format(as.Date(dataset$GIVEN_DATE), '%Y-%m-%d') 

2: If you want to create a new column (of String type) by concatenating values from two other columns:

  select OLD_COL_1 || ', ' || OLD_COL_2 as NEW_COL from dataset 

GitHub Link to Excel Files and PowerBI Dashboard: GitHub

References

% R Packages that are not supported in PowerBI

% Create an Empty DAX Table

  CustomTable1 = DATATABLE ( "Other Measures", INTEGER, {{0}}) 

Sorting an 'HTML Content' Visual in PowerBI

First, we see our "Visualization" from our previous post: PowerBI's HTML Content Visualization




Next, we also drag a second column 'DERIVED_DT' (the derived date) into the 'HTML Content Visual'.




It has no impact on the visualization.

In the above image, the rows are not sorted according to the 'date'. 

To sort it, we have to click on "ellipsis" (...) and we see the menu with 'Sort By' option:




We also have options to select sorting order: "ascending" or "descending".
We will select "descending" and revert the sorting order.




Link to PowerBI File (.pbix): GitHub

Ref: PowerBI's HTML Content Visualization