Forum Discussion

markive's avatar
markive
Advocate 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
rensblom's avatar
rensblom
Frequent Visitor
8 years ago

Hello all,

 

I came across this post because I experienced similar error message and followed all the proposed solutions here, but I keep getting the error above. What I try to acomplish is the following: I have a list of F1 drivers with their social media screennames in a SQL table (DimDriver), first I would like to add number of followers, tweets etc from the Twitter API. What I did was follow the oAuth method form this blog https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/ and put it all in a custom function.
Next I made a query referencing my DimDriver( renamed it directly to "twitterdata"), filtered on all driver with a screenname in the table and as a last step I invoked a custom column using the above function. As far as I can see that makes only one external data source, but still no luck. Could it be the custom function?

 

Any help would be appreciated, the F1 reports are openly available on our companies website for training purposes, if anyone is interested!

 

The code I used:

 

let
Source = Twitterdata,
#"Removed Columns" = Table.RemoveColumns(Source,{"Driver", "Reference", "Number", "Code", "Birthdate", "Nationality", "WikipediaURL", "Hashtag last name", "Hashtag code", "Facebook", "Instagram"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Twitter] <> null)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Twitter", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Twitter.1", "Twitter.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Twitter.1", type text}, {"Twitter.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Twitter.1"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns1", "TwitterFunction", each TwitterFunction([Twitter.2]))
in
#"Invoked Custom Function"

Many thanks in advance!

 

Rens

the2nicks's avatar
the2nicks
Advocate 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

  • mussol_'s avatar
    mussol_
    Advocate II
    8 years ago

    Solved just using "Merge" as soon as possible on the query.

     

    Seems that editor doesn't like to do merges by the end of the query as it was clearly explained on excelguru blog.

     

    Josep.