How to write to DATA sources with Power Query?

Question

In Excel, we can use Power Query to read from different data sources. Then we can do things like append and merge. But after that, sometimes we want to put the appended table back into the data source. How can we do this?

For example,

I have 2 files, one.csv and two.xls

In my master.xls file, I have imported both of these files using Power Query then merged them together.

Now how do I export this merged table as one.csv (or two.xls)?

I can manually copy and paste, but I imagine Excel should have to data export tool for that?

Thanks!

Shi Yang Shi Yang · Accepted Answer · 2018-05-31T16:36:27

Power Query can not export data directly,but it can be achieved indirectly.

1.Load your query table to the Data Model,and use DAX Studio to connect it,then click Output-File.

2.M+JS is possible,you can use JS in M,but you should enable ActiveX scripts in IE first.

I'm sorry that my system language is Chinese,hope you can find it.

If the two tables are merged in Source,the code is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Json = Text.FromBinary(Json.FromValue(Table.ToRows(Table.DemoteHeaders(Source)))),
    Export = Web.Page("<script>
                            var fso=new ActiveXObject('Scripting.FileSystemObject');
                            var f1=fso.CreateTextFile('C:/Users/rages/Desktop/test.csv',true);
                            var arr="&Json&";
                            f1.WriteLine(arr.join('\n'));
                            f1.WriteBlankLines(1);
                            f1.Close(); 
                    </script>")
in
    Export

Pay attention to modifying the path,if it works,test.csv will be generated.

How to write to DATA sources with Power Query?

2 Answers