Forum Discussion

AUDISU's avatar
AUDISU
Resolver III
4 years ago

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's avatar
ethanlsaul
Helper I
4 years ago

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"