CODEWITHSUNDEEP
excelpowerbipowerpivotpowerquery
need_java
need_java

Reputation: 127

Power Query Function - Optional Arguments - Token Literal Expected

I am trying to create a function in powerquery that will accept optional arguments. The purpose of the function is to establish a common directory that will be used for text imports in other queries within the workbook. Typically for our models, we have a Parent Directory that has the following structure:

Financial_Ind_Ovr_Dashoards
    Data
        -Data_File_1.txt
        -Data_File_2.txt
        -Data_File_3.txt
    Models
        -Model1.xlsm
        -Model2.xlsm

Sometimes, though, either the Data or Models directory will be named or nested differently.

These parent directories are constantly moved from place to place while keeping the structure. Due to this, I am trying to get a relative file path for our import queries. With that being said, I am getting an error with my code:

let
    GET_DATA_PATH = (optional dir_nest_level as number, optional data_dir_path as text) =>

    //Handle Input
    cur_dir_nest_level = if dir_nest_level = null or dir_nest_level = "" then 1
                                                                         else dir_nest_level,

    data_dir_path_from_parent_dir = if data_dir_path = null or data_dir_path = "" then "Data\"
                                                                                  else data_dir_path,


    //Get Path
    full_path = Excel.CurrentWorkbook(){[Name="WORKBOOK_PATH"]}[Content]{0}[Column1],
    separators = Text.PositionOf(full_path, "\", Occurrence.All),
    dir_count = List.Count(separators),
    sep_index = dir_count - 1 - cur_dir_nest_level,
    GET_DATA_PATH = Text.Start(full_path, separators{sep_index} + 1) & data_dir_path_from_parent_dir
 in
    GET_DATA_PATH

When writing this as a regular query (rather than function) I know that my logic after the //Get Path comment works fine. After adding the logic to make this a function and handle the input, I keep getting the following error:

Expression.SyntaxError: Token Literal expected.

This error occurs on the first if statement where I am referencing the cur_dir_nest_level variable. I'm not really familiar with power query functions so I'm sure I am doing something incorrect. Any and all insght would be greatly appreciated.

Upvotes: 1

Views: 4622

Answers (1)

Aldert
Aldert

Reputation: 4313

You have forgotten the extra let you need:

let
    GET_DATA_PATH = (optional dir_nest_level as number, optional data_dir_path as text) =>
     let
        //Handle Input
    cur_dir_nest_level = if dir_nest_level = null or dir_nest_level = "" then 1
                                                                         else dir_nest_level,

    data_dir_path_from_parent_dir = if data_dir_path = null or data_dir_path = "" then "Data\"
                                                                                  else data_dir_path,


    //Get Path
    full_path = Excel.CurrentWorkbook(){[Name="WORKBOOK_PATH"]}[Content]{0}[Column1],
    separators = Text.PositionOf(full_path, "\", Occurrence.All),
    dir_count = List.Count(separators),
    sep_index = dir_count - 1 - cur_dir_nest_level,
    GET_DATA_PATH = Text.Start(full_path, separators{sep_index} + 1) & data_dir_path_from_parent_dir
    in
        GET_DATA_PATH
in
    GET_DATA_PATH

Upvotes: 1

Related Questions