Search code examples
pythonpandaspowerbipowerquerym

how to use the tables generated by Python/R in Power BI?

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?

Solution

  • 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