Export parsed-XML dataset as CSV - R Script

[littletinner]

I am parsing a single XML file, at the end of which I would like to export the 'parsed' data to a .csv file.

While I can successfully export the data using 'Run R Script' at the end of the query using the script below, it seems to clear out my data in PowerBI, thus breaking all my visualizations.

write.table(dataset, file="c:/AcadTemp/filename.csv", sep = ",", row.names = FALSE);

How do I write .csv output at the end of the 'query' and still have the query end with the original dataset (table) that was written to the .csv file?

Also, if i could script it to export a .csv file with the same name as the .pbix file, what would be equally awesome.  I will be reusing the same powerbi 'template' for multiple jobs by changing the 'Source' file and 'saving as'.  This when the dataset is refeshed, I would like the visualizations to update along with a freshly exported .csv file.  So, FileA.pbix would export FileA.csv, while

FileB.pbix would export FileB.csv, etc. 

[v-ljerr-msft]

Hi @littletinner,

Based on my test, we need to change the the power query in Advanced Editor to use the previous step instead of  step #"Run R Script" as output as suggested by smoupre above, then it should work as expected.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvRxUdJRMjTTNTAF0QYGSrE6yKJmQNoIKuoXcmgBMkTSaIpXBcgQM1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"State   " = _t, #"YYMM  " = _t, #"Amount                         " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State   ", type text}, {"YYMM  ", type text}, {"Amount                         ", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"State   ", "State"}, {"YYMM  ", "YYMM"}, {"Amount                         ", "Amount"}}),
    #"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)write.table(dataset, file=""C:/Export Files/filename.csv"", sep = "","", row.names = FALSE);#(lf)dataset;",[dataset=#"Renamed Columns"])
in
    #"Renamed Columns"

For the sceond question, after a few try, I still cannot figure out a way to get the currently pbix file name with R or M in this scenario. Hope others who are more familar with R or M could help.

Regards

[Greg_Deckler]

You need to make sure that the "in" portion of your Power Query includes a step that contains your dataset. I am guessing that the write.table function does not return the dataset so if it is the step referenced in your "in" of your Power Query then you will get nothing. Just use the Advanced Editor to change it to the step above that statement.