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

[Trebor76]

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:

//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

 

[sandy666]

i think first you should read this

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[sandy666]

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

 

[sandy666]

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]

Thanks sandy666. I have noticed from previous posts you know your stuff when it comes to Power Query.

Regards,

Robert

 

[sandy666]

You are welcome & Thanks for the feedback

Welcome to the mysterious Power Query Art Zone