Place Power Query Result in a Separate Worbook?

[snaplink22]

Hello,

I am pulling 6 tabs worth of data into one table using Excel Power Query, however, I would like to Close & Load the combined data to a separate workbook and not the one where the data is currently stored at. Is this possible?

Thank you.

 

[sandy666]

open new 2nd workbook, do: (data - new query - from file - from workbook) get data from the 1st workbook where is your source
then load data into the sheet in the 2nd workbook

 

[snaplink22]

Thank you Sandy, however, I ran into another question while messing around with the Power Query.

When I am pulling data from 2 or more separate workbooks into one tab on a blank workbook all the headers appear below each subsequent table and I am unable to delete them. All my workbooks have the same column headers, but how do I go about just pulling over the data after the first query pulls over the header into row A?

 

[sandy666]

if your source data has the same structure and the same headers just Append them and you will get single table

 

[snaplink22]

Still learning how to combine multiple workbooks with different names and those workbooks have different tabs and varying tab names as well. I've created a function to pull the first of me three files from my designated file folder, but when I attempt to invoke the function with the second file, labelled "WA_Cons" it cannot locate it for obvious reasons (see below function).

let
Source = (WA) => let
Source = Excel.Workbook(File.Contents("C:\Users\Benjamin Thiel\Desktop\New folder\" &WA), null, true),
SS_Sheet = Source{[Item="SS",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(SS_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"
in
Source

I've tried playing around with a few options, but I cannot ascertain what the code should be to include the WA_Cons.xlsx or WA_Lin.xlsx files when I invoke the function. The first file is named WA_SS.

Any thoughts?

 

[sandy666]

with all *.xlsx files in one folder, use From Folder feature

 

[snaplink22]

Sorry, in place of what though? My guess would be instead of Item="SS" it would read Item="From Folder"?

 

[sandy666]

three files from my designated file folder

try , eg. Source = Folder.Files("D:\test\TestFolder"),
maybe it will be better than with separated files

 

[snaplink22]

I saw what you mentioned before and redid my power query to pull from the file instead of the file. Here is my new function:

let
Source = (WA) => let
Source = Folder.Files("C:\Users\Benjamin Thiel\Desktop\New folder\" & WA),
Data_Sheet = Source{[Item="???",Kind="Sheet"]}[WA],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name"})
in
#"Removed Other Columns"
in
Source

However, I'm still not sure what to include in this code in order to be able to invoke the function to pull data from any of the 3 files I have.

 

[snaplink22]

*Folder instead of file.