Is there a way to write a log when a power query is being executed from the power bi service

[nedpbi]

Wondering if there is a way to write to the log when a power bi query is being executed during the model refresh in power bi service.

Or any other way to trace in detail all the query calls from the power bi model when the model is being refreshed in the service.

Thank you,

[Greg_Deckler]

@nedpbi I'm not aware of any way to do that. The only thing that I can think of would be some creative use of metadata that you could then return as data in your table. Would have to think about how to do that. What kind of information are you trying to log?

[nedpbi]

Thanks @Greg_Deckler for that tip. I did try to use this to return some metadata in the same table as the result table but does get cumbersome to code and skews the data in case of partial errors but probably will try writing to another log table using a function maybe. Unfortunately I guess this table cannot be hidden so I can report on the logs. Was looking for a generic way to log all query calls mainly to understand how the incremental refresh works and sends queries to the backend when published to the power bi service. 

Another way I was exploring was using a web service to pass some logs or message data to the backend or using an azure function.

But i guess there is no standard functionality to debug such issues with query refreshes in power bi service ?

[Greg_Deckler]

@nedpbi I am not aware of any generic solution for this. It is kind of "roll your own" as far as I can tell. I agree that the meta data approach is cumbersome, so is creating a record to hold that data like the following. I do like the web service idea.

let
    Source = Sql.Database("localhost", "AdventureWorksDW2022"),
    dbo_FactInternetSales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
    a = [ Step1Time = DateTime.LocalNow(), Step1Rows = Table.RowCount(dbo_FactInternetSales) ],
    #"Removed Other Columns" = Table.SelectColumns(dbo_FactInternetSales,{"ProductKey", "CustomerKey", "SalesTerritoryKey", "SalesOrderNumber", "UnitPrice", "ExtendedAmount", "DiscountAmount", "ProductStandardCost", "TotalProductCost", "SalesAmount", "TaxAmt", "Freight", "OrderDate", "DueDate", "ShipDate"}),
    b = Record.AddField(a , "Step2Time", DateTime.LocalNow()),
    c = Record.AddField(b, "Step2Rows", Table.RowCount(#"Removed Other Columns")),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [OrderDate] < #datetime(2012, 1, 1, 0, 0, 0)),
    d = Record.AddField(c , "Step3Time", DateTime.LocalNow()),
    e = Record.AddField(d, "Step3Rows", Table.RowCount(#"Filtered Rows")),
    Final = Table.AddColumn( #"Filtered Rows", "Debug", each e),
    #"Expanded Debug" = Table.ExpandRecordColumn(Final, "Debug", {"Step1Time", "Step1Rows", "Step2Time", "Step2Rows", "Step3Time", "Step3Rows"}, {"Step1Time", "Step1Rows", "Step2Time", "Step2Rows", "Step3Time", "Step3Rows"})
in
    #"Expanded Debug"

[nedpbi]

Thank you @Greg_Deckler this is useful.

I was also looking at the Diagnostics.Trace M function but looks like this does not work in power bi service.

I will try the above and get back. 

Thank you again for the code snippet !