Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Friends, hello
I have an Excel spreadsheet that allows the user to select a given date that is used on an existing connection, that will go to a SharePoint location retrieve a database content for that particular date. Once the user selects the date, the individual needs to refresh an existing connection.
The individual does that, refreshes the connection and it works just fine. Goes to a sharepoint location, get the relevant databse file content, manipulates a bit and loads into the Excel spreadsheet.
Now the question:
"How can I, parallel to loading/running the existing query, ALSO shows/get/loads the query executed timestamp when it finishes loading/running?"
let
#"dbFile" = Excel.CurrentWorkbook(){[Name="dbFile"]}[Content]{0}[Column1],
#"dbPath" = Excel.CurrentWorkbook(){[Name="dbPath"]}[Content]{0}[Column1],
#"getFile" = Web.Contents(Text.Combine({#"dbPath",#"dbFile"})),
#"importTXT" = Table.FromColumns({Lines.FromBinary(#"getFile",null,null,TextEncoding.Ascii)}),
#"splitByDelimiter" = Table.SplitColumn(importTXT, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv)),
#"promoteHeaders" = Table.PromoteHeaders(#"splitByDelimiter", [PromoteAllScalars=true]),
#"replaceValues" = Table.ReplaceValue(#"promoteHeaders",null,"",Replacer.ReplaceValue,Table.ColumnNames(#"promoteHeaders"))
in
#"replaceValues"
Using VBA I can create a trigger based event to prompt the current date/time when refreshing the existing connection, but I'm trying to not involve VBA on this one.
Is it possible to do what I'm trying to without VBA, using only Query M?
Hi @fmcristaldi ,
I think the generally-accepted method is to create aonther query in PQ that's just:
= DateTime.FixedLocalNow()
You can then output this query somewhere in your spreadsheet. It's not going to be EXACTLY when the other query ends, but it's usually good enough to give end-users a steer on how fresh their data is.
Pete
Proud to be a Datanaut!