Hi there!
This maybe a relatively simple answer, but I cant seem to make this work.
I am wanting to make a dynamic source depending on whether the file it local or web based.
FileType cell in Excel has a dropdown box with "Web" or "File". I am wanting the have the source and change to either "Web.contents(FilePath & FileName)" or "File.contents(FilePath & FileName)"
When i run it with the source with the code typed in as either Web or File the query works fine. I just can seem to make it run with the web/file being dynamic.
Source = Excel.Workbook(Web.Contents(FilePath & FileName), null, true), - works fine
Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true), - works fine
Source = Excel.Workbook(FileTypeX, null, true), - Does not work
Code below:
let
FilePath = Excel.CurrentWorkbook(){[Name="FILEPATH"]}[Content]{0}[Column1],
FileName = Excel.CurrentWorkbook(){[Name="CTRM_FILE"]}[Content]{0}[Column1],
FileType = Excel.CurrentWorkbook(){[Name="FILETYPE"]}[Content]{0}[Column1],
FileTypeX = Text.From(FileType&".Contents(FilePath & FileName)"),
Source = Excel.Workbook(FileTypeX, null, true),
COST_CODES_LIST_Table = Source{[Item="COST_CODES_LIST",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(COST_CODES_LIST_Table,{<!-- -->{"Cost code", type text}, {"Description", type text}})
in
#"Changed Type"
