Invalid binary code - Advanced Editor Power Query

Question

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"

Answer 1

Hi there. Power Query is no longer supported here on Q&A. Please post your question over on https://aka.ms/PQCommunity instead.