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

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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)
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
Thanks sandy666. I have noticed from previous posts you know your stuff when it comes to Power Query.

Regards,

Robert
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome & Thanks for the feedback

Welcome to the mysterious Power Query Art Zone :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,682
Messages
5,626,272
Members
416,170
Latest member
Urraco

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
Top