Cant index or search parameter list in PowerBI

Objective: I am querying parameters exported from Revit in PowerBI, with the goal of identifying if specific parameters exist, and if they do exist, do they have a value or not?

The Revit models that we extract the information from have a set of parameters (should all have that is) which we need to identify. So far I am able to find where they are, however they are named by default when going the [data][parameters] route with a random identifier from Revit. The only way to get the names that I expect to be seeing is by using the Speckle.Revit.Parameters.ToNameValueRecord([data][parameters]) method.

This works for step 1, however the issue is that I cant query that record list for specific parameters. I would expect to be able to query such as ValueRecord[“PARAMETER NAME HERE”] or the sort, but any attempt at doing so just yields syntax errors. Is there something I am missing that should allow me to query the names that are output directly (and their corresponding values)?

I have tried the expand route, and selecting the columns to keep, however this will inevitably run into the issue that the query is based on the parameter actually existing in the first place, and it is uncertain if the parameter will remain the same name across multiple models/versions/etc. The end-goal is just a simple column that queries into the ValueRecords and checks:
-Does this parameter exist in this list?
-If it exists, does it have a value (show value if exists, otherwise use “EMPTY”)
-If it doesn’t exist, flag with a failure state (say assign null, or use “N/A”)

Basically I have a list of 12 parameters to check for if they exist or not, and expanding for example only yielded 11, which would mean one is missing, but the expand method would not work to help identify which one is missing. Any help or thoughts on how to approach this would be appreciated.

1 Like

Hi @EugeneStol and welcome to our community!
Feel free to Introduce yourself :person_gesturing_ok: if you’d like.

I believe you need to use the internal Revit name for that query, so if “AZ Area/Zone/Block” is a shared parameter, you should use its GUID. If it’s a default revit parameter, it’d be something like “PHASE_CREATED”.

These are not currently displayed in the new web app, but if you open your model in the old web app you should be able to see such values in the scene explorer:

Let me know if that works!

Hey @EugeneStol ,

It looks like you solved half of the problem, which is converting your parameter records into simple key-value pairs. The second part. is more of a Power Query question, checking if a key exists in a record and then extracting its value.

Power Query has a method that allows you to check if a Record has a given field. Give your parameters record as the record object, and the field you are looking for, which is your parameter’s name.

This will return a True/False. And you can do an IF statement with this value.

Give this a try. If you can’t figure it out, please share the Speckle Model URL with us.

I thought this would work, but it seems even that option is also explicit sadly. I cant figure out a way to get it to search the field names for a string contains type of function, as it only gives any result if its a 1:1 match. This would work for known and constant parameters but since I have parameters that may or may not exist, and may or may not have slight variations to its suffix, I still cant figure out if its possible to isolate the names.

If the ToNameValue Record didn’t Include the brackets of its unique identifier somehow, or if i could erase that suffix, then I could make it work. Since it seems to be a PowerBI question I will try to see if their forums may have something.