Parsing through list values in a parameter

[asubr]

I have assigned a list to a parameter in Power Query -

2015 meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type any, List = {2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022}]

How do you parse through the multiple values? I see the each in the invoke custom function, but it brings in only one of the files in the preview. "FileName" is the name of the Parameter in the query.

let

FullName = "File Name"&Number.ToText(FileName)&".csv",

  Source = SharePoint.Files("Folder Path", [ApiVersion = 15]),

  #"Invoked custom function" = Table.AddColumn(Source, "Invoked custom function", each #"Transform file"(FileName)),

  Navigation = #"Invoked custom function"{[Name = FullName, #"Folder Path" = "Folder Path"]}[Content],

  #"Imported CSV" = Csv.Document(Navigation, [Delimiter = "|", Columns = 7, QuoteStyle = QuoteStyle.None]),

  #"Promoted headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars = true])

in

  #"Promoted headers"

Thanks for any help.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @asubr, I think you are asking if Power Query parameter could include multiple selections.  The answer is yes and no.  Power Query parameters do not allow a list of values as a parameter value.  However, you can consider using the parameter as text string that contains the following examples:

FileName = "2015"

FileName = "2015, 2016"

FileName - "2015, 2016, 2017"

The trick is to convert the Text string into a Table with rows.

let
    Source = #table(type table[FileName=text], { {FileName} }),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"FileName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "FileName"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"FileName", Text.Trim, type text}})
in
    #"Trimmed Text"

You now need to add the custom function to this table using this modified function

(FileName as text) as table => 
let
  FullName = "File Name" & FileName & ".csv",
  //Please refer to the SharePoint Site rather than the Folder Path.
  Source = SharePoint.Files(#"SharePoint Site", [ApiVersion = 15]),
  //I have added a Text Contains to select the folder.  You could use the full path or end with instead.
  #"Get File" = Table.SelectRows(Source, each [Name] = FullName and Text.Contains( [FolderPath] , "Folder Path" ) ),
  #"Get Csv" = #"Get File"{0}[Content],
  #"Imported CSV" = Csv.Document(#"Get Csv", [Delimiter = "|", Columns = 7, QuoteStyle = QuoteStyle.None]),
  #"Promoted headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars = true])
in
  #"Promoted headers"

[asubr]

Thanks so much Daryl!

You can assign the parameter as a list though, but looks like there is no way to iterate through the list in the parameter? The only solution is to treat it as text string and get it into a table and then iterate?

I hope i understood it correctly.