Excel Tab Name Changes

[ethanlsaul]

Hello,

I get a new data file (Excel) each week. The file gets saved over last week's report (Same file name). The Excel tab name changes to match the week #. It has the naming covention Week ## (ex Week 12). 

Because the excel tab is different each week, it fails unless i manually change the name in excel or i edit the power query code.

[danextian]

Hi @ethanlsaul ,

If the sheet tab is always in the same order - like first in the order among all sheets and tables the workbook has, you can  use the sheet position instead of the sheet name with 0 being the first one in the order. Instead of the code below

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\Book3.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

You can change it to

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\Book3.xlsx"), null, true),
    Sheet1_Sheet = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Source{0}[Data] tells power query to load the first table from Data column from the first step which is Source.

[ethanlsaul]

Hi there,

Here is what i have. The Orange Bold writing is the sheet name that will constantly change. This time it is 32, next week will be 33. it is the only sheet in the file. 

let
Source = SharePoint.Files("https://jnj.sharepoint.com/teams/OneStopShop", [ApiVersion = 15]),
#"J&J Kroger OvS xlsx_https://jnj sharepoint com/teams/OneStopShop/Shared Documents/Kroger/" = Source{[Name="J&J Kroger OvS.xlsx",#"Folder Path"="https://jnj.sharepoint.com/teams/OneStopShop/Shared Documents/Kroger/"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"J&J Kroger OvS xlsx_https://jnj sharepoint com/teams/OneStopShop/Shared Documents/Kroger/"),
#"UPC Matrix (32)_Sheet" = #"Imported Excel Workbook"{[Item="UPC Matrix (32)",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"UPC Matrix (32)_Sheet",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"

[AUDISU]

Hi @ethanlsaul ,

You need to load that excel workbook to power query using "Folder" option.
Then power query will get data from all tabs in that excel. When you save new file in next week, power query will automatically detect the new tab with new week number and will get data from that new tab as well.

Try to load data from "Folder",

If you have any issue let me know. I will tell you step by step.

Thank you.

[ethanlsaul]

Hi,

Not sure how to do a folder...i have this right now: The Orange Bold writing is the sheet name that will constantly change. This time it is 32, next week will be 33. it is the only sheet in the file. 

let
Source = SharePoint.Files("https://jnj.sharepoint.com/teams/OneStopShop", [ApiVersion = 15]),
#"J&J Kroger OvS xlsx_https://jnj sharepoint com/teams/OneStopShop/Shared Documents/Kroger/" = Source{[Name="J&J Kroger OvS.xlsx",#"Folder Path"="https://jnj.sharepoint.com/teams/OneStopShop/Shared Documents/Kroger/"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"J&J Kroger OvS xlsx_https://jnj sharepoint com/teams/OneStopShop/Shared Documents/Kroger/"),
#"UPC Matrix (32)_Sheet" = #"Imported Excel Workbook"{[Item="UPC Matrix (32)",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"UPC Matrix (32)_Sheet",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
#"Promoted Headers"

[Ashish_Mathur · Accepted solution]

Hi,

Follow the steps shown in this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.

[ethanlsaul]

The technique of removing the tab names in this view worked like a charm! Thanks!!

[Ashish_Mathur]

You are welcome.  Thanks to the creator of the video.

[Arul]

@ethanlsaul ,

You have to change the code everytime when the tab name changed. Please refer the below thread,

https://community.powerbi.com/t5/Power-Query/Changing-Excel-sheet-name-in-Power-Query/m-p/2483242 

Thanks,

Arul