Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

If statement in Power Query Advanced Editor.

Hi there, 

 

This is probably a relatively simple answer, but I have tried it a few ways with no success. 

I am wanting to build in a an if fuction to check if the file is local or web based before sourcing the info. 

This is what i have currently come up with, but i continue to get SyntaxError: Token Identifier expected.

 

Essentialy, I want the source to change between web and file depending on whether i am accessing the server or the localaly saved files. Ie. Source = Excel.Workbook(**Web/File**.Contents(FilePath & FileName), null, true)

 

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],

if FileType="Web" then Source = Excel.Workbook(Web.Contents(FilePath & FileName), null, true) else if FileType="file" then Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true)else null

EQUIPMENT_LIST_Table = Source{[Item="EQUIPMENT_LIST",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(EQUIPMENT_LIST_Table,{{"ID", type text}, {"DESCRIPTION", type text}, {"COMPANY_ID", type text}, {"Cost Type", type text}, {"RATE", Int64.Type}, {"CHARGE TYPE", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "DESCRIPTION", "COMPANY_ID", "Cost Type", "RATE", "CHARGE TYPE"})
in
#"Removed Other Columns"

 

Any help would be much appreciated 🙂

 

  • Hi Anonymous 
    please write it like so instead:
     Source = if FileType="Web" then Excel.Workbook(Web.Contents(FilePath & FileName), null, true) else if FileType="file" then Excel.Workbook(File.Contents(FilePath & FileName), null, true) else null

     

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 
    please write it like so instead:
     Source = if FileType="Web" then Excel.Workbook(Web.Contents(FilePath & FileName), null, true) else if FileType="file" then Excel.Workbook(File.Contents(FilePath & FileName), null, true) else null

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks Imke!

       

      That did the trick 🙂