Dynamic Parameter for appending tables based off of servers

[jfenico]

Currently I have a report where each server we query is a parameter. When someone wants to create a new report, they load up the template and it will prompt them for the name of the servers to query. While this works, the problem we are running into is if the number of servers is different from what the template is looking for (e.g. the template is looking for 3 servers but the report is only need to report on 2).

This is due to an appended table looking not being happy with null values (where the other servers would exist).

Is there a way to basically say

If there are X amount of servers, only append X amount of tables together?

[v-yuezhe-msft]

@jfenico,

Would you please describe more details about how you define the parameter and post the code in Advanced Editor that you use to append the tables? Sample data is appreciated.

Regards,
Lydia

[jfenico]

Hi @v-yuezhe-msft - It's been awhile since this post has been recreated but I found myself looking for the solution again. So, without further adieu, here's some more info.

Right now, the report is set to allow for multiple parameters (5 total). Each parameter respresents a server where there are MySQL tables I am querying. The type of MySQL data in these tables is specific to that server, however, the report I have takes all of the data from all servers and appends them into one total table. Sometimes however, the report may only need to report on 2 servers or 3 servers or 1 server. The number of servers changes.

Right now I have a crud (but working) If else statement that checks to see if the parameters are entered starting at parameter 5 and working down to 1. If any of the parameters matches another, it will result in skipping an append for that table. For example, if parameter 5 matches the same server name as parameter 1, then it will check to see if parameter 4 matches 1, then 3, then 2. If at any time a paramater does not match the initial, it will combine the correct number of tables together. See below for the statement:

= if Server5 <> Server1 then Table.Combine({Project_items, #"Project_items (2)",#"Project_items (3)",#"Project_items (4)",#"Project_items (5)"}) else if Server4 <> Server1 then Table.Combine({Project_items, #"Project_items (2)",#"Project_items (3)",#"Project_items (4)"}) else if Server3 <> Server1 then Table.Combine({Project_items, #"Project_items (2)",#"Project_items (3)"}) else if Server2 <> Server1 then Table.Combine({Project_items, #"Project_items (2)"}) else Table.Combine({Project_items})

To reiterate my original post, what I would like to do is make this more dynamic. Instead of a long If Else statement, it would be nice if I can check to see what parameters are filled first, then append the tables based off of that.