Hi, I have a data source in my report which uses power query to connect to the Twitter API - this refreshes fine on the desktop but once published to the service it tells me "Your data source can't be refreshed because the credentials are invalid. Please update your ceredentials and try again."
I've tried updating the credentials to use Anonymous sign in - as this is what's used on PBI Desktop but it doesn't work. My code is below, any suggestions?
Thanks /*
/*
This M script gets an bearer token and performs a tweet search from the Twitter REST API
https://dev.twitter.com/oauth/application-only
Requires establishing a Twitter application in order to obtain a Consumer Key & Consumer Secret
https://apps.twitter.com/
IMPORTANT - The Consumer Key and Consumer secret should be treated as passwords and not distributed
*/
let
// Concatenates the Consumer Key & Consumer Secret and converts to base64
authKey = "Basic " & Binary.ToText(Text.ToBinary("***MY TOKEN***:***MY SECRET***"),0),
url = "https://api.twitter.com/oauth2/token",
// Uses the Twitter POST oauth2/token method to obtain a bearer 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),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
// Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method
GetJsonQuery = Json.Document(Web.Contents("https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=***MY TWITTER SCREEN NAME***", [Headers=[Authorization=AccessTokenHeader]])),
#"Converted to Table" = Table.FromList(GetJsonQuery, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"created_at", "id", "id_str", "text", "truncated", "entities", "source", "in_reply_to_status_id", "in_reply_to_status_id_str", "in_reply_to_user_id", "in_reply_to_user_id_str", "in_reply_to_screen_name", "user", "geo", "coordinates", "place", "contributors", "is_quote_status", "retweet_count", "favorite_count", "favorited", "retweeted", "lang", "possibly_sensitive", "retweeted_status", "quoted_status_id", "quoted_status_id_str", "quoted_status", "extended_entities"}, {"Column1.created_at", "Column1.id", "Column1.id_str", "Column1.text", "Column1.truncated", "Column1.entities", "Column1.source", "Column1.in_reply_to_status_id", "Column1.in_reply_to_status_id_str", "Column1.in_reply_to_user_id", "Column1.in_reply_to_user_id_str", "Column1.in_reply_to_screen_name", "Column1.user", "Column1.geo", "Column1.coordinates", "Column1.place", "Column1.contributors", "Column1.is_quote_status", "Column1.retweet_count", "Column1.favorite_count", "Column1.favorited", "Column1.retweeted", "Column1.lang", "Column1.possibly_sensitive", "Column1.retweeted_status", "Column1.quoted_status_id", "Column1.quoted_status_id_str", "Column1.quoted_status", "Column1.extended_entities"}),
#"Expanded Column1.entities" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.entities", {"hashtags", "symbols", "user_mentions", "urls", "media"}, {"Column1.entities.hashtags", "Column1.entities.symbols", "Column1.entities.user_mentions", "Column1.entities.urls", "Column1.entities.media"}),
#"Expanded Column1.retweeted_status" = Table.ExpandRecordColumn(#"Expanded Column1.entities", "Column1.retweeted_status", {"created_at"}, {"created_at"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.retweeted_status",{{"created_at", "original tweeted at"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column1.geo", "Column1.coordinates", "Column1.place", "Column1.contributors", "Column1.is_quote_status", "Column1.favorited", "Column1.retweeted", "Column1.lang", "Column1.possibly_sensitive", "Column1.quoted_status_id", "Column1.quoted_status_id_str", "Column1.extended_entities"}),
#"Expanded Column1.user" = Table.ExpandRecordColumn(#"Removed Columns", "Column1.user", {"location"}, {"location"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Column1.user",{"Column1.in_reply_to_user_id_str", "Column1.in_reply_to_status_id_str", "Column1.in_reply_to_user_id", "Column1.in_reply_to_status_id", "Column1.truncated", "Column1.entities.hashtags", "Column1.entities.symbols", "Column1.entities.user_mentions", "Column1.entities.urls", "Column1.entities.media", "Column1.source", "Column1.id"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Is Retweet?", each if Text.StartsWith([Column1.text], "RT") then "Yes" else "No"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Conditional Column", "Column1.created_at", Splitter.SplitTextByEachDelimiter({"+0000"}, QuoteStyle.Csv, false), {"Column1.created_at.1", "Column1.created_at.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.created_at.1", type text}, {"Column1.created_at.2", Int64.Type}}),
#"Extracted Text Range" = Table.TransformColumns(#"Changed Type", {{"Column1.created_at.1", each Text.Middle(_, 0, 10), type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Text Range", {{"Column1.created_at.2", type text}}, "en-GB"),{"Column1.created_at.1", "Column1.created_at.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Column1.id_str", "Tweet ID"}, {"Column1.text", "Tweet Text"}, {"Column1.in_reply_to_screen_name", "Replying to"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Date", "Tweet ID", "Tweet Text", "Replying to", "Column1.retweet_count", "Column1.favorite_count", "original tweeted at", "Is Retweet?", "location"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Column1.retweet_count", "Retweet Count"}, {"Column1.favorite_count", "Favourite Count"}, {"original tweeted at", "Original Tweeted At"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns2", "Original Tweeted At", Splitter.SplitTextByEachDelimiter({"+0000"}, QuoteStyle.Csv, false), {"Original Tweeted At.1", "Original Tweeted At.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Original Tweeted At.1", type text}, {"Original Tweeted At.2", Int64.Type}}),
#"Extracted Text Range1" = Table.TransformColumns(#"Changed Type2", {{"Original Tweeted At.1", each Text.Middle(_, 0, 10), type text}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Text Range1", {{"Original Tweeted At.2", type text}}, "en-GB"),{"Original Tweeted At.1", "Original Tweeted At.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Original Tweeted At"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Original Tweeted At", type date}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type3",{"Date", "Tweet ID", "Tweet Text", "Replying to", "Retweet Count", "Favourite Count", "Is Retweet?", "Original Tweeted At", "location"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns1", "Tweet URL", each "https://twitter.com/mojo_bar/status/"&Text.From([Tweet ID])),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Favourite Count", Int64.Type}, {"Retweet Count", Int64.Type}}),
#"Expanded Column1.quoted_status" = Table.ExpandRecordColumn(#"Changed Type4", "Column1.quoted_status", {"created_at", "text"}, {"Column1.quoted_status.created_at", "Column1.quoted_status.text"})
in
#"Expanded Column1.quoted_status"
