Forum Discussion

KLJ's avatar
KLJ
Helper I
8 years ago
Solved

Need help with R script

Hi

 

I have two tables in my report. In query editor I have merged them and then I want to save the result to a text file.

 

I have tried to add this script to the query Itemlist:


require(gdata)
write.table(trim(dataset), file="C:/Users/KLJ/Documents/Power BI/Test10.txt", sep = "\t", row.names = FALSE, append = TRUE)
plot(dataset);

 

but I get this message:

(Sorry for the bad picture)

 

Is there a R script that can save this query to a text file?

R scrips is very new to me. 

 

Here is a link to a copy of the pbix file

 

Thanks in advance.

 

Best regards 

Kim

Need Help

5 Replies

  • stretcharm's avatar
    stretcharm
    Memorable Member
    8 years ago

    I've used R to export data a few times from both ther editor and the visuals

     

    This R script works for me.

     

    # 'dataset' holds the input data for this script

write.table(dataset, file="C:\\temp\\Test.txt", sep = "\t", row.names = FALSE, append = TRUE)

output <- dataset

    The M for my test looks like this.

     

        Source = Table.NestedJoin(Table2,{"Column1"},Table2,{"Column1"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column2", "Column3"}, {"Table2.Column2", "Table2.Column3"}),
    #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)write.table(dataset, file=""C:\\temp\\Test.txt"", sep = ""\t"", row.names = FALSE, append = TRUE)#(lf)#(lf)output <- dataset",[dataset=#"Expanded Table2"]),
    #"""output""" = #"Run R Script"{[Name="output"]}[Value]
in
    #"""output"""

    Trim in r doesn't work on a dataframe. So if you need to trim either do it before you export in M or use sapply functions in r.

    M is easiest, but the R option might be quicker with very large datasets.