Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This is likely simple, but I'm not getting it.
We have a database with multiple clients on the server. So we named the schemas after each one. I read through the ability to use Query Parameters and I created 3 different Parameters, Server, Database, and Schema. Server and Database are working fine. Its getting me to the correct machine and the correct database (Prod, QA, Test) ... But when I try to use the Schema parameter on the queries, its throwing the type conversion.
So I started with this:
TheClientName{[Name="distinctcve"]}[Data]
And I want to change it to this
Schema{[Name="distinctcve"]}[Data]
However, Schema is not a list. How do I get it to substitute the value in Schema, for the variable name ?
Thanks
Solved! Go to Solution.
Hi @Anonymous
the Schema is a column in a table and you'd select it like so:
AdventureWorks2008R2{[Schema="HumanResources",Item="vEmployee"]}
attaching the [Data]-column like you did above will return the Data-values in list form.
So if you're using a parameter for the schema name called "MySchemaParameter", then the syntax would look as follows:
AdventureWorks2008R2{[Schema=MySchemaParameter,Item="vEmployee"]}[Data]
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you - Both of your suggestions we needed to complete the picture, so let me explain.
When I tried your solution, the Schema=Variable came back and said it could not resolve. There was no "column" for Schema.
Then I tried the next solution, which was exactly what I tried before but as before, it confused the variable with the structure.
Then I realized you both had a point. I always use the option to use the full heirarchy.
However when you do that, it eliminates Schema as a column ! So the filter did not work. When I redid the query WITHOUT the full heirarchy, it is a column and is now selectable using the option. Thank you. So your answer was right as long as you dont select by full heirarchy.
Thank you both
Hi @Anonymous
the Schema is a column in a table and you'd select it like so:
AdventureWorks2008R2{[Schema="HumanResources",Item="vEmployee"]}
attaching the [Data]-column like you did above will return the Data-values in list form.
So if you're using a parameter for the schema name called "MySchemaParameter", then the syntax would look as follows:
AdventureWorks2008R2{[Schema=MySchemaParameter,Item="vEmployee"]}[Data]
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you - Both of your suggestions we needed to complete the picture, so let me explain.
When I tried your solution, the Schema=Variable came back and said it could not resolve. There was no "column" for Schema.
Then I tried the next solution, which was exactly what I tried before but as before, it confused the variable with the structure.
Then I realized you both had a point. I always use the option to use the full heirarchy.
However when you do that, it eliminates Schema as a column ! So the filter did not work. When I redid the query WITHOUT the full heirarchy, it is a column and is now selectable using the option. Thank you. So your answer was right as long as you dont select by full heirarchy.
Thank you both
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.