Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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