Invoked Custom Function - SQL Queries

umutkoyun

New member
Joined
Jun 30, 2022
Messages
6
Reaction score
0
Points
1
Excel Version(s)
365
Hi All,

Anyone knows any way around to run an update which includes custom query functions on the BI server ?


#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "fnQuery", each fnQuery([Query])),


(I plan to move all such queries to the SQL as views, any other ideas ?)


Thanks,
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I'm going to say "it depends"...

What does the query do? How is it written (i.e. what can you show me the function's M code? And are you talking about setting it up to schedule the refresh in the Power BI service without using a gateway?

Generally the answer would be to make sure that you are using the Value.NativeQuery() function to send a parameterized query to SQL server that doesn't violate the formula firewall. What you want to avoid is building a query in text and then passing it via the Sql.Database() function, as that just causes issues. Not sure if that would block you from a refresh on the service, but it also wouldn't surprise me.

On the other hand, if you have access to the SQL server to build views, then having a view built on the SQL server just makes life so much easier as Power Query is just retrieving data and can fold any filters to make retrieval faster.
 

umutkoyun

New member
Joined
Jun 30, 2022
Messages
6
Reaction score
0
Points
1
Excel Version(s)
365
hi Ken,

Nice to be in contact with you again.

I think I got this method from one of your posts like 5-6 years ago.

Here is the first part of the code

Code:
  #"Added Conditional Column" = Table.AddColumn(#"Removed Duplicates", "Query", each " use "& dBase &"


select 

*

from  ["& dBase &"_"& Text.From([Yil]) &"_"& [FirmaID] &"].[ACCOUNT_CARD_ROWS] ACR

left join ["& dBase &"_"& Text.From([Yil]) &"_"& [FirmaID] &"].[ACCOUNT_PLAN] AP on ACR.[ACCOUNT_ID] = AP.[ACCOUNT_CODE]
left join ["& dBase &"_"& Text.From([Yil]) &"_"& [FirmaID] &"].[ACCOUNT_CARD] AC on AC.CARD_ID = ACR.CARD_ID


"),
    #"Invoked Custom Function" = Table.AddColumn(#"Added Conditional Column", "fnButunSantiyeler", each fnButunSantiyeler([Query])),
#"Expanded fnButunSantiyeler" = Table.ExpandTableColumn(#"Invoked Custom Function",

fnButunSantiyeler Function Query

Code:
 (Query as text) => let
        Source = Sql.Database(dBaseIP, dBase , [Query=Query, CreateNavigationProperties=false])
    in
        Source

Ekran görüntüsü 2022-07-01 080836.png


Even with the gateway, it does not update the queries which includes Invoke Function and yes, i am trying to update automatically without the gateway and manually with Power Automate. All gives Privacy Errors and not updating.

I am not uisng Value.NativeQuery() function though. Will it solve my Privacy problem and let me refresh ?

Thanks :canada:
 

umutkoyun

New member
Joined
Jun 30, 2022
Messages
6
Reaction score
0
Points
1
Excel Version(s)
365
I have tried with this code but it does not run the query for each row (though it does not recognize the [Query] column)

Code:
NPowerQuery = Sql.Database(dBaseIP, dBase),
Code:
WholeData = Value.NativeQuery(NPowerQuery, [Query],null,[EnableFolding = true] )


also I tried below code as I have to run the Query for each row. It is working normally but still does not update on the server side.


Code:
let
    Source = (Query as text) => let
        Source = Value.NativeQuery(Sql.Database(dBaseIP, dBase), Query)
    in
        Source
in
    Source


Microsoft.Data.Mashup.MashupSecurityException.Reason PrivacyError
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hmmm... give this a shot and let me know if it makes any difference:

M:
let
    Source = (Query as text) => let
        myDB =Sql.Database(dBaseIP, dBase),
        Source = Value.NativeQuery(mdDB, Query)
    in
        Source
in
    Source

If not, I'm actually wondering if declaring the myDB outside the function as a separate parameter (or query) might help...
 

umutkoyun

New member
Joined
Jun 30, 2022
Messages
6
Reaction score
0
Points
1
Excel Version(s)
365
Hmmm... give this a shot and let me know if it makes any difference:

M:
let
    Source = (Query as text) => let
        myDB =Sql.Database(dBaseIP, dBase),
        Source = Value.NativeQuery(mdDB, Query)
    in
        Source
in
    Source

If not, I'm actually wondering if declaring the myDB outside the function as a separate parameter (or query) might help...
what is the difference with my code (which I have already tried) on my previous message ?

let
Source = (Query as text) => let
Source = Value.NativeQuery(Sql.Database(dBaseIP, dBase), Query)
in
Source
in
Source
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
It is breaking the query down a bit into separate steps. There are times when this makes a big difference to Power Query’s evaluation of the function. (Especially when trying to deal with issues related to the formula firewall, which is what you are seeing.)
It may not fix it, but it could. Give it a go a let me know if it makes any difference.
 

umutkoyun

New member
Joined
Jun 30, 2022
Messages
6
Reaction score
0
Points
1
Excel Version(s)
365
It is breaking the query down a bit into separate steps. There are times when this makes a big difference to Power Query’s evaluation of the function. (Especially when trying to deal with issues related to the formula firewall, which is what you are seeing.)
It may not fix it, but it could. Give it a go a let me know if it makes any difference.
unfortunately it didnt make any difference...

what about this new feature ? they use the parameter bind to a table in the source query...

Dynamic M query parameters in Power BI Desktop


i dont have any options left i guess... i have to force the option to create views on SQL with IT team...
 
Top