Forum Discussion
Helper IAdding query hints in M (Vertica)
Community SupportHi SorenHElisa
Do you need to add comments in M query?
If so, please add "// balabala"
https://yodalearning.com/tutorials/top-steps-to-do-comments-in-query-steps/
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IHi Maggie,
Thanks for your response, but this was unfortunately not what I was looking for.
I need the "comment" to go down to the database, i.e. the comment need to be part of the generated SQL. So when Power BI tranlsates the qurey to be run in the database, the format should be:
select /*+ label('blahblah') */ col1 from table1;
Can this be done?
Br,
Sören
- v-juanli-msft
Hi SorenHElisa
When you connect to sql sever with power bi desktop, you can choose tables/columns from sql database on a pop-out window.
This would then send query like "select balabala from table bb".
Or you could write sql query in advanced statement under the connnection information.
When you import data, open Transform data, on the right pane, select one step and right-click on it, you would see "view native query" where shows sql queries sending to sql database.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- SorenHElisa
Hi Maggie,
Yes, I'm aware of the functionality with the columns and how to show the native query. But these functionalities does not give you the opportunity to give a query hint.
And since the header of this discussion intantiate that it is Vertica (not SQL Server) that we are using as backend, the functionality might differ.
I was thinking, are there any M-language functions that we can use?
Since the query hint must be placed at a special point in the query, i.e. right after the word "select" and before any column names, "distinct" or aggregation functions.
It seems that M-language cannot handle these kind of scenarios. Am I right?
Example:
select
/*+ label('PowerBI: Customer invoice sums') */
t1.customer_id,
t2.customer_name,
sum(t1.amount)
from fct_invoice t1
inner join dim_customer t2 on t1.customer_id = t2.customer_id
where t1.invoice_date between '2020-05-01' and '2020-05-31'
group by t1.customer_id, t2.customer_name
This can be tracked on basis of label "PowerBI: Customer invoice sums" inside Vertica and get execution time, tables involved, user running the query etc etc.
As I stated in the original message, we can of course directly input the query in the SQL field when creating the connection, but it would be nice to be able to add the labelling if users have used Power Query functionality to build the query.
So, is this possible in M-language / Power Query editor?
Would be nice to be able to do something like:
...
Labelled_query = Table.QueryHint(Query,"label('PowerBI: Invoice report')"),
...
and it would generate the sql code accordingly.
Br,
Sören