1

I am trying to use Python/R in Power BI. But I do now know how to use the table generated by Python. I do not even know where it is. Here is an example:

id  name    score
1   zhao    65
2   qian    98
3   sun 100
4   li  45
5   zhou    98

Let's say this is my data called id. I use this button enter image description here to read the data into Power BI.

Then I use this button enter image description here to start the Power Query Editor, from where I can use Python/R by clicking enter image description here in Transform.

Then I have a very simple Python code in Run Python script:

# 'dataset' holds the input data for this script
import pandas as pd

myid=dataset
myid.loc[:,'id']=myid.loc[:,'id']*100

Everything goes correctly until now. But you might notice that I use 'dataset' to represent the data, instead of the real data name 'id'. So I am wondering if the database name is always 'dataset' by default in Power BI?

When I finish coding, I have this:

enter image description here

You can see there is no new table called 'myid' in the left part. Furthermore, when I use the button enter image description here to apply this change, there are some changes happened in Power BI Desktop:

enter image description here

You can see there is no any new table here either. So, what I should do if I want to use Python generated table to do something else, such as drawing, or using another Python analysis somewhere else?

Or, maybe this is not the correct way to use Pythong in Power BI? So, if I want to use Python to analyze data and model them in Power BI. And the result can be used in Power BI for visualization, etc afterwards, what I should do?

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
Feng Chen
  • 2,139
  • 4
  • 33
  • 62

1 Answers1

1

Here's what the M code looks like for an R script.

let
    Source = Table.FromRows({{1,"zhao",65},{2,"qian",98},{3,"sun",100},{4,"li",45},{5,"zhou",98}},{"id","name","score"}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)myid <- dataset#(lf)myid$id <- 100*myid$id",[dataset=Source]),
    myid = #"Run R script"{[Name="myid"]}[Value]
in
    myid

The code in the R script window is simply

# 'dataset' holds the input data for this script

myid <- dataset
myid$id <- 100*myid$id

When you enter that script it should create a new step or two.

R Script

Simply click on the Table in the Value column to drill down to that table (if it didn't do that automatically).

Drilldown

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • That is great! Could you please answer two more questions: 1. When I finish coding in R script window. The M code automatically changes. So I do not need to coe sth in M code by meself, right? 2. If I want to use the new table (myid here) in Power BI destop, for example, visualization. How can I do it? Now when I click 'Close and Apply', I still cannot see the new table in Power BI Desktop. Soory to bother, I just start studying Power BI for 2 days. – Feng Chen Feb 26 '19 at 04:58
  • 2
    1. Not. You shouldn't need to use the Advanced Editor if you don't want to. 2. The table shown in the last applied step should be the same table that loads into Power BI. If you want to load both tables, then duplicate the query and only run the script on one copy. – Alexis Olson Feb 26 '19 at 05:04
  • This really helps a lot. Thanks – Feng Chen Feb 26 '19 at 06:06