Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jfenico
Helper III
Helper III

Dynamic Parameter for appending tables based off of servers

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?

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.