Forum Discussion
Power Query only refreshes after refreshing preview
I'm using Power Query in Excel. I've created a query to extract data from Anaplan via APIs. The query works, but I can't refresh normally.
Using Refresh All triggers a query refresh, but the actual data is not updated. If I edit the query, refresh the preview of the first step (the authentication step) and close and load, the data updates.
For some reason, Power Query is re-using the old value of the first step for authentication (in other words, the old authentication token). How do I force Power Query to refresh all the steps in my query?
let
// Get a new authentication token.
mytoken1 = (let
auth_key = "Basic " & GetValue("Encoded_Auth"),
url = "https://auth.anaplan.com/token/authenticate",
header = [#"Authorization" = auth_key,
#"Content-Type" = "application/json"],
webdata1 = Web.Contents(url, [Headers=header,Content= Text.ToBinary("")]),
response1 = Json.Document(webdata1),
tokenInfo = "AnaplanAuthToken " & response1[tokenInfo][tokenValue]
in
tokenInfo),
// Trigger data download in model.
#"WorkspaceID" = GetValue("WorkspaceID"),
#"ModelID" = GetValue("ModelID"),
#"ExportID" = GetValue("ExportID_Movement"),
#"auth_key2" = mytoken1,
url2 = "https://api.anaplan.com/2/0/workspaces/" & #"WorkspaceID" & "/models/" & #"ModelID" & "/exports/" & #"ExportID" & "/tasks",
header2 = [#"Authorization" = #"auth_key2",
#"Content-Type" = "application/json"],
content2 = "
{
""localeName"" : ""en_US""
}
",
webdata2 = Web.Contents(url2, [Headers=header2, Content= Text.ToBinary(content2)]),
response2 = Json.Document(webdata2),
// Check column count when downloading file.
// Pause and then download data from model.
Stop1 = () => Csv.Document(Web.Contents("https://api.anaplan.com/2/0/workspaces/" & #"WorkspaceID" & "/models/" & #"ModelID" & "/files/" & #"ExportID" & "/", [Headers=[Authorization=#"auth_key2", #"Content-Type"="application/csv; charset=UTF-8", Accept="application/octet-stream"]]),[Delimiter=",", Columns=16, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Source = Function.InvokeAfter(Stop1, #duration(0,0,0,10)),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
// Stop here when copying query.
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"L3# Deals: Active", type text}, {"L3# Deals: Active: Code", type text}, {"L3# Deals: Active: Asset Class", type text}, {"L3# Deals: Active: LOB", type text}, {"L3# Deals: Active: Product Type", type text}, {"L3# Deals: Active: Product Sub Type", type text}, {"Client Fee Types", type text}, {"Fundraising Teams", type text}, {"Sources", type text}, {"Investor Types", type text}, {"Time", type date}, {"Year", type text}, {"NCY Fundraising (Excl. Confirmations)", Currency.Type}, {"NCY Confirmed Only", Currency.Type}, {"NCY Fundraising (Incl. Confirmations)", Currency.Type}, {"USD Fundraising (Excl. Confirmations)", Currency.Type}})
in
#"Changed Type"
I solved the issue, but it raises another question!
The process to download data from Anaplan is as follows:
- Authenticate and get an authentication token. This is used in future API requests.
- Trigger the download process in Anaplan. This only starts the download on the server side. No data is sent to Power Query.
- Download the data (from step 2).
- Transform data as required.
Step 2 above is represented by this line:
response2 = Json.Document(webdata2),
This line is then no longer used in the query. All this does is start the download process on the server side, it is not used again in the query. Because it is not used, I don't think Power Query bothers to run it when refreshing the query. Why would it? The line doesn't impact the query. BUT, from an API perspective, I need this to be updated each time.
This is why refreshing the preview worked, it forced Power Query to update this value and hence re-trigger the download process.
5 Replies
This didn't resolve the issue unfortunately.
That is odd. A quick test I would do would be to copy your mytoken1 into a separate query with a different name, and then reference that query instead.
Pat
Agreed weird behaviour. It seems that Power Query is being "lazy" and re-using the old authentication token. I tried this but it didn't work. Tried both as a function and query.
I solved the issue, but it raises another question!
The process to download data from Anaplan is as follows:
- Authenticate and get an authentication token. This is used in future API requests.
- Trigger the download process in Anaplan. This only starts the download on the server side. No data is sent to Power Query.
- Download the data (from step 2).
- Transform data as required.
Step 2 above is represented by this line:
response2 = Json.Document(webdata2),
This line is then no longer used in the query. All this does is start the download process on the server side, it is not used again in the query. Because it is not used, I don't think Power Query bothers to run it when refreshing the query. Why would it? The line doesn't impact the query. BUT, from an API perspective, I need this to be updated each time.
This is why refreshing the preview worked, it forced Power Query to update this value and hence re-trigger the download process.
