Get & Transform Data (Power Query) - Export Query to a Sheet

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
5,260
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I've started learning the functions within the Get & Transform Data tab of the Data ribbon. As such I created two named ranges ("FilePath" and "FileName") to import a file from which I've pieced together from different threads.

Though the below doesn't error out it's also not outputting to a sheet:

VBA Code:
//Power Query: Load data using Named Ranges
let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col A", Int64.Type}, {"Col B", Int64.Type}, {"Col C", Int64.Type}, {"Col D", Int64.Type}, {"Col E", Int64.Type}})
in
    #"Changed Type"

I actually have a few questions:

1. What is this reference Sheet1_Sheet
2. Can I use a named range to set which tab I import instead of hard keying it like this Item="Sheet1"
3. How do I tell the query to output the record set to a certain tab (say Sheet5)
4. Is correct to say "Power Query" or "Get & Transform Data"

Many thanks in advance.

Regards,

Robert
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
1. What is this reference Sheet1_Sheet
2. Can I use a named range to set which tab I import instead of hard keying it like this Item="Sheet1"
3. How do I tell the query to output the record set to a certain tab (say Sheet5)
4. Is correct to say "Power Query" or "Get & Transform Data"
@1 - this is the name of the step, can be blabla or JohnTravolta
@2 - you have to figure out how to do it or post#2
@3 - Close&Load to... (or choose the target place then from Queries pane - right click and load to)
@4 - MS changed Power Query to Get&Transform in XL2016 (before it was Power Query add-in for XL2010/2013 with Power Query name on the Excel ribbon) but most ppl use Power Query. It's up 2U
 
Last edited:
Upvote 0
post#3@ @1 - in most cases the step name is given automatically depending on what you are doing
post#3@ @2 - you can use Parameter(s)
 
Upvote 0
Thanks sandy666. I have noticed from previous posts you know your stuff when it comes to Power Query.

Regards,

Robert
 
Upvote 0
You are welcome & Thanks for the feedback

Welcome to the mysterious Power Query Art Zone :)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top