Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Ok datanuts, I'm scratching my head. I've been googling everywhere to answer what I assumed to be an easy novice question but no luck...so looking for help.
I have an ODBC-based connection to a denodo server. That server has many services, and I can connect to them without issue. My problem is that one of them has a mandatory parameter it requires a value for, and for the life of me I can't figure out how to provide it.
once I open the advanced editor, I have something that looks like this:
let
Source = Denodo.Contents("DenodoODBC", null),
worker_Database = Source{[Name="worker",Kind="Database"]}[Data],
worker_Schema = worker_Database{[Name="worker",Kind="Schema"]}[Data],
TheOneIWantToSee_View = worker_Schema{[Name="TheOneIWantToSee",Kind="View"]}[Data]
in
TheOneIWantToSee_View
I've tried using a WHERE in here, and I can't find any syntax documentation on being able to provide that in the view row.
I've tried going to the database properties, and adding in an SQL line, and also connection properties for this mandatory field, but nothing is working.
any suggestions?
Solved! Go to Solution.
so I eventually figured this out. it was a syntax and quoting issue with VDL based language, as well as me trying to do a direct native query vs. using a linked server syntax (openquery style), which has lots of google'd reference syntax. What I used, in case anyone else searches, is:
= Odbc.Query("dsn=DenodoODBC", "SELECT * FROM ""MyTable"" WHERE ""FieldN"" > 0")
Hi @gckcmc ,
You can do this by using the table function Table.SelectRows in Power Query. Please refer to the linked Microsoft documentation. Reference: https://community.denodo.com/answers/question/details?questionId=9060g0000000BkrAAE&title=Required+P....
If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
Thanks. I would usually use Table.SelectRows as a subsequent step after getting an initial table from the Source. In this case, I can't filter after getting the source because it fails in the source. The referenced link you provided to denodo community answers effectively the same thing, with very similar source to mine. e.g.:
(just referencing that code in the link since it's the same problem as mine):
let
Quelle = Denodo.Contents(“DenodoODBCa”, null),
shopfloor_Database = Quelle{[Name=”shopfloor”,Kind=”Database”]}[Data],
shopfloor_Schema = shopfloor_Database{[Name=”shopfloor”,Kind=”Schema”]}[Data],
my_data_View = shopfloor_Schema{[Name=”my_data”,Kind=”View”]}[Data]
in
my_data_View
This is effectively the Navigation step post source connection to the database. (which, without mandatory parameters, would yield a beginning table).
Are you suggesting to avoid the navigation step, and go directly to a Table.SelectRows in lieu of, or are you suggesting adding a Table.SelectRows in the code above? (which would act as the "WHERE") (like this:)
let
Quelle = Denodo.Contents(“DenodoODBCa”, null),
shopfloor_Database = Quelle{[Name=”shopfloor”,Kind=”Database”]}[Data],
shopfloor_Schema = shopfloor_Database{[Name=”shopfloor”,Kind=”Schema”]}[Data],
my_data_View = shopfloor_Schema{[Name=”my_data”,Kind=”View”]}[Data],
Table.SelectRows(my_data_View, each [someVariable] > 2
in
my_data_View
if so, I don't think that works....
Oops...realized why that wouldn't work...updated to:
let
Quelle = Denodo.Contents(“DenodoODBCa”, null),
shopfloor_Database = Quelle{[Name=”shopfloor”,Kind=”Database”]}[Data],
shopfloor_Schema = shopfloor_Database{[Name=”shopfloor”,Kind=”Schema”]}[Data],
my_data_View = shopfloor_Schema{[Name=”my_data”,Kind=”View”]}[Data],
FooBar = Table.SelectRows(my_data_View, each [someVariable] > 2
in
FooBar
ok so that passes syntax, but still gives me the same error that the "someVariable" effectively isn't being recognized as (I think) the my_data_View executes before the Select rows, and still wants to see the mandatory variable before it gets to the FooBar line.
Is there a way to put the Table.SelectRows as part of the Schema row, to pass in as a variable?
so I eventually figured this out. it was a syntax and quoting issue with VDL based language, as well as me trying to do a direct native query vs. using a linked server syntax (openquery style), which has lots of google'd reference syntax. What I used, in case anyone else searches, is:
= Odbc.Query("dsn=DenodoODBC", "SELECT * FROM ""MyTable"" WHERE ""FieldN"" > 0")
Hi there,
I am late here but worth discussing it
I am currently in the same scenerio now , use of ODBC connector instead of a Denodo connector would work here to pass parameters but wouldnt that make your query to Import instead of Direct ? I couldnt see an option to select storage type while using ODBC and i do not see a advance box for Denodo connector to enter custom query.
I think there is a limiation of using direct query and parameter together, please clarify
Thanks
Any connection gurus out there?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |