variable sheet name

utkuacik · ‎06-19-2023

hi guys .

i am searching for the answer for long time but could not find any solution. i just created dynamic folder and excel file name in power query . bu i just want to make the sheet name also variable. i just wish to take the sheet name form an excel table also . Name of the table for the variable sheet name will be "TCE_sheet_name" AND WİLL HAVE 1 Column with name "TCE_sheet_name".

so i just wish to make the value below as a variable , coming from a table.

i will be looking forward for your kind replies

best regards

let
Source = Excel.CurrentWorkbook(){[Name="Table55"]}[Content], ////path to folder Which excel file located
link = Source{0}[Table55_column_name],
GetFiles = Excel.Workbook(File.Contents(link & "test.xlsm")), //// show sheets in excel file
TCE_Sheet = GetFiles{[Item="TCE_test_sheet",Kind="Sheet"]}[Data], /// OPEN the sheet with name "TCE_test_sheet"
#"Promoted Headers" = Table.PromoteHeaders(TCE_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"

slorin · ‎06-19-2023

Hi

Something like this

...
GetFiles = Excel.Workbook(File.Contents(link & "test.xlsm")),
Sheet_name = Table.FirstValue(Excel.CurrentWorkbook(){[Name="TCE_sheet_name"]}[Content]),
TCE_Sheet = GetFiles{[Item=Sheet_name,Kind="Sheet"]}[Data],
...

Stéphane