Forum Discussion

markive's avatar
markive
Icon for Advocate II rankAdvocate II
10 years ago
Solved

Formula.Firewall: Query references other queries, so it may not directly access a data source.

I am getting the following error:     Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this dat...
Need Help
the2nicks's avatar
the2nicks
Icon for Advocate I rankAdvocate I
8 years ago

It's hard (for me) to tell without seeing it "properly" but there doesn't seem to be anything wrong.

The only thing I can say is that I now have ALL data loads in separate functions ... I added a function which gets passed a sheet/range name and it loads the data.

I see that you have Source=Twitterdata ... what is Twitterdata, it doesn't seem to be a function as it doesn't have () closing brackets.

Apart from that this is a simple script which I would have hand-coded as a single multi-line statement so I'm rather puzzled.

Hope you sort it out. Good luck.

rensblom's avatar
rensblom
Frequent Visitor
8 years ago

Hey the2nicks,

 

Actually TwitterData is referring to another Query, the function is invoked on the last line 

#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns1", "TwitterFunction", each TwitterFunction([Twitter.2]))

The code for the function is:

 

let FxGetTwitterUserData = (ScreenName as text) =>

let
 authKey = "Basic " & Binary.ToText(Text.ToBinary("<<here are my twitter API keys ;-)>>"),0),
 url = "https://api.twitter.com/oauth2/token",
 GetJson = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("grant_type=client_credentials") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken,
 GetJsonQuery = Web.Contents("https://api.twitter.com/1.1/users/lookup.json?cursor=-1&screen_name="&ScreenName,
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
 FormatAsJsonQuery = Json.Document(GetJsonQuery),
    FormatAsJsonQuery1 = FormatAsJsonQuery{0},
    #"Converted to Table" = Record.ToTable(FormatAsJsonQuery1),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "description" or [Name] = "favourites_count" or [Name] = "followers_count" or [Name] = "following" or [Name] = "follow_request_sent" or [Name] = "friends_count" or [Name] = "screen_name" or [Name] = "status" or [Name] = "statuses_count" or [Name] = "url")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"screen_name", type text}, {"description", type text}, {"url", type text}, {"followers_count", Int64.Type}, {"friends_count", Int64.Type}, {"favourites_count", Int64.Type}, {"statuses_count", Int64.Type}, {"status", type any}, {"following", type any}, {"follow_request_sent", type any}})
in
    #"Changed Type"
in
    FxGetTwitterUserData



Of course it's nicer to hand code, but this was (I think) the 4th try to get it working, so I'll clean up later

 

Thanks for your reply