Power Query - Replicating select all in parameter cell value

[mamclero]

Hello,

I was wondering if someone could help with an issue I'm having with my parameter in Power Query. I've created a table in excel I want to be the parameter value, which I've referenced in the filter in Power Query. It works fine when I select one value, but is there a way to select multiple or all values for my query using just the cell? I've searched around and tried to find something, but haven't had much luck. Thanks in advance!

I'm using Excel 2016.

 

[MarcelBeug]

Something like this?

B1 is Named Range Parameter.

Query Parameter splits the parameter value into a table:

let
    Source = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
    Splitted = Table.TransformColumns(Source,{{"Column1", each Text.Split(_,","), type list}}),
    #"Expanded Column1" = Table.ExpandListColumn(Splitted, "Column1"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1", "Select"}})
in
    #"Renamed Columns"

Query Input imports the Input table into Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"SomeText", type text}})
in
    #"Changed Type"

Query Output filters the Input, unless the first parameter value = "All", in which case the entire Input is returned:

let
    Source = Input,
    #"Merged Queries" = Table.NestedJoin(Source,{"SomeText"},Parameter,{"Select"},"Parameter",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Parameter"}),
    Result = if Table.FirstValue(Parameter) = "All" then Source else #"Removed Columns"
in
    Result