Forum Discussion

lekkerbek's avatar
lekkerbek
Helper IV
2 years ago

Parameter retrieving multiple sources

Hi,

 

I'm retrieving financial information from a database where administration have different division numbers. When I want to create a report I filter on division codes. That filter needs to be added to every table I retrieve. To make things easier I wonder if it is possible to create a parameter for this.

 

Currently the code is like this:

 

#"Filtered Rows" = Table.SelectRows(#"ExactOnlineREST.System.ExactInfo@eol_table", each [Division] = 1234 or [Division] = 5678),

 

Red is obviously the relevant part and in this example it's 2, but could also be more than 2 divisions.

I already tried to create a parameter and it does work for 1 division code. However it doesn't work when I enter multiple division codes.

 

Any thoughts?

 

Need Help

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi lekkerbek ,

     

    For this you can use the solutions given by this video https://www.youtube.com/watch?v=KnvSAAqfUX0

     

    In this case what I did was to create a parameter :

    The I changed the code of the filter by the following:

    = Table.SelectRows(#"Changed Type", each (List.Contains ( 
let
    ParameterSource = #"Division Parameter",
    ParameterTable = #table(1, {{ParameterSource}}),
    ParameterListValues = Table.ToList( Table.ExpandListColumn(Table.TransformColumns(ParameterTable, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"))
in
    ParameterListValues
, [Division] ) ))

     

    Full code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcq7DQAgDMTQXa6mCZ8As0TZfw2sVLh7kiNkajJStlAHiwoDXCrMf1vAqeDgUGGDTcp8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Division = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each (List.Contains ( 
            let
                ParameterSource = #"Division Parameter",
                ParameterTable = #table(1, {{ParameterSource}}),
                ParameterListValues = Table.ToList( Table.ExpandListColumn(Table.TransformColumns(ParameterTable, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"))
            in
                ParameterListValues
            , [Division] ) ))
in
    #"Filtered Rows1"