cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelF1
Helper I
Helper I

Changing Excel sheet name in Power Query

Hi Everyone

 

I have a Power Query data source that is drawing multiple excel files from the same folder. I need to add to the files in the folder to capture the latest monthly sales data for my organisation. No problem here.

 

However, the way in which the source data is generated has now changed and is being supllied to me with a changed 'Tab name'.

 

Originally, when I set up the original power query, each sheet had the same name as shown here:

 

Capture8.PNG

With corresponding step in Power Query:

 

Capture6.PNG

Now my data source has changed and so the tab name has changed:

 

Capture9.PNG

 

And so I now get this error when I try to load the data:

Capture7.PNG

I can't do anything about the new files but I can go back and change the names of the old ones to 'something_esle'.

 

But, how do I change my Power Query query so that it will accept the change in Tab name? I can't find it referenced anywhere in the advanced editor.

 

Thanks very much in advance,

 

Michael

 

 

 

 

 

 

1 ACCEPTED SOLUTION

@MichaelF1 The code change needed is to the Transform File function.

 

It should look similar to this:

AlexisOlson_0-1651173499391.png

This is where you can modify it as @Vijay_A_Verma helpfully suggested.

View solution in original post

8 REPLIES 8
Vijay_A_Verma
Community Champion
Community Champion

You need not change previous ones's names. Look here, I am merging few Excel workbooks in a folder where sheets are named either Sheet1 or Sheet2. So, you will need to change Transform Sample File code with a try statement. Replace Sheet1 and Sheet2 appropriately.

let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet1_Sheet = try Source{[Item="Sheet1",Kind="Sheet"]}[Data] otherwise Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

Hi @Vijay_A_Verma , thanks for the reply.

I'm confused becuse my source is a folder, not a workbook...

let
    Source = Folder.Files("S:\file_path\sales_orderdate"),

etc

Thanks again,

 

Michael

 

Look on left side and you will find Transform Sample File, you will need to make this change there. See the pic below

1.png

@MichaelF1 The code change needed is to the Transform File function.

 

It should look similar to this:

AlexisOlson_0-1651173499391.png

This is where you can modify it as @Vijay_A_Verma helpfully suggested.

Hi, @AlexisOlson 

Yes, that's what I need. Thank you, I didn't know to look in the Transform File!

Thanks v much for your help

 

Michael

 

ronrsnfld
Solution Sage
Solution Sage

It would be helpful if you could post the code from the Advanced Editor (as text, not as a screenshot).

Hi @ronrsnfld ,

Here you go...

let
Source = Folder.Files("my_file_path\sales_orderdate"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"SalesOrders.CompanyCode", Int64.Type}, {"Company Name", type text}, {"SalesOrders.CreatedDate", type date}, {"SalesOrders.CustOrdRef", type text}, {"SalesOrders.OrderNumber", Int64.Type}, {"SalesOrders.OrderType", type text}, {"SalesOrders.AccountCode", type text}, {"vSalesOrders.vOrderClass", type text}, {"Vcreatedtime", type datetime}, {"SalesOrders.OrderValue", type number}})
in
#"Changed Type"

 

Thank you

 

Michael

 

 

Hi @MichaelF1 ,

 

You are looking at the query that applied invoke function. when you use the "Import from folder" function, PQ will create sample file, transform file, and the invoked query (i.e. Test3 in your case).

 

#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),

 

You will need to look at the "Transform Sample file" query as per @Vijay_A_Verma note.

KT_Bsmart2gethe_0-1651198900843.png

 

if you post the code for the Transform Sample file, and we will be able to guide you from there.

 

Regards

KT

 

 

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors