Forum Discussion

useless's avatar
useless
New Member
4 years ago
Solved

R script

Please don't link to https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-r-in-query-editor   I am just trying to get output matching the input. I am using a .csv as a source as that web...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Try clicking on the resulting Table in the Value column.

     

    You could also select that cell at this step by appending "{0}[Value]".

    #"Run R script" = R.Execute("output <- dataset",[dataset=Source]){0}[Value],

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

Please share your M code from the Advanced Editor so that we can replicate what you're seeing.

  • useless's avatar
    useless
    New Member
    4 years ago

    Thanks for responding. I copied and pasted it below. I had to remove some stuff like file path and some column names etc just for privacy. I added screenshot of before / after the step.

     

     

    let
    Source = Csv.Document(File.Contents("C:\Users\____\Documents\____\___Table.csv"),[Delimiter=",", Columns=34, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"id", Int64.Type}, {"date", type date}, {"time", type text}, {"outcome_1", type text}, {"outcome_2", type text}, {"duration", type text}, {"file_name", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)output <- dataset",[dataset=Source]),
    #"Removed Columns" = Table.RemoveColumns(#"Run R script",{""}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}})
in
    #"Changed Type1"

     

     

     

     

    For context, my actual goal in the R script was to read a different .csv, run some logic, add some columns to the current table, then have it propagate into Power BI for visualisation, whilst also saving a truncated version to a third .csv.

    I get how to do it in R, I just don't understand why the data is getting nested into a field before I do anything to it.

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      Try clicking on the resulting Table in the Value column.

       

      You could also select that cell at this step by appending "{0}[Value]".

      #"Run R script" = R.Execute("output <- dataset",[dataset=Source]){0}[Value],

       

      • useless's avatar
        useless
        New Member
        4 years ago

        Hi Alexis,

        I think I figured out what had happened - and it's exactly as you suggested. I had deleted a step without realising it's importance.

         

        After any Run R Script, there needs to be a follow up of equivalent:

         

        = #"Run R script"{[Name="output"]}[Value]

         

        as the next step to kind of navigate into the nested table or can use the code snippet you provided - mine is just the one auto-generated when I tried it again.