BI export: Wrike connection problems

Good morning, 

 

I am trying to use the "BI export" with Power Bi.

I have followed the following page instructions:

 

So, what i have done is:

Make a connector to the web "https://www.wrike.com/api/v4/data_export" with my token:

After transforming the response obtained, I get the following:

 

These URLs have the following format:

https://storage.www.wrike.com/data_export/resource/11025...8?accountId=5...6

 

Then, I try to connect (using a from web connector) to one of this URLs using an Authorization "bearer mytoken":

The problem is that I get an error message:

The message says...:

Not possible to connect

There was found an error while connecting.

Details: "The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all types of authentication: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type , If-Modified-Since, Prefer, Referer "

 

So... What am I doing wrong?

Appart from that, how could I change the real value of the URL (that is going to change every day) to the value of the following table, similar to a variable name? I will have one table for each URL:

 

Thank you very much for your help,

 

Marc

1
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos
19 comentarios
Avatar
Hugh

Hi Marc!

I've raised a Support Ticket for you, you should receive an email soon and our team will be able to help you with this.

If you have any other questions let me know! 😊

1
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hi Hugh, 

I have the same question as Marc. I got to the name of the table and the storage.www.wrike.com url (see below). How do I get to the columns and data behind the url? 

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Yasmeen Wilde I have the the Wrike data connected to Power BI but unfortunately my method doesn't allow for automatic refreshing of the data. Hopefully this can point you in the right direction though.

I started by getting the location of each of the Wrike CSV documents:

let
Source = Json.Document(Web.Contents("https://www.wrike.com/api/v4/data_export", [Headers=[Authorization="bearer token"]])),
data = Source[data],
data1 = data{0},
resources = data1[resources],
#"Converted to Table" = Table.FromList(resources, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "url"}, {"Column1.name", "Column1.url"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.url", "url"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Table from Link", each Csv.Document(Web.Contents([url], [Headers=[Authorization="bearer token"]]),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.Csv]))
in
#"Added Custom"

Then I created a new query for each CSV link (Table from link):

let
Source = #"Wrike Data",
#"Table from Link" = Source{1}[Table from Link],
#"Promoted Headers" = Table.PromoteHeaders(#"Table from Link", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"audit_log_id", Int64.Type}, {"operator_user_id", Int64.Type}, {"audit_log_object_type", type text}, {"audit_log_object_id", Int64.Type}, {"audit_log_object_title", type text}, {"audit_log_operation_type", type text}, {"audit_log_operation_time", type datetime}, {"audit_log_operation_origin_location_country", type text}})
in
#"Changed Type"

4
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Seth Neds it works, thank you so much for sharing your code. 

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Yasmeen Wilde You're welcome! I'll glad you got it figured out.

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Seth Neds Thanks for the M query/Power query tip here, the second level of authorization by URL I'm still wrapping my mind around.  Elegant solution that should go into the Wrike documentation.

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Seth Neds I was able to get all of the queries to pull data from the links. Thank you!

 

Question- How do I get these queries to refresh? The queries aren't refreshing every hour/day. 

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Jacob Wright I apologize for this delayed response.

 

Question- How do I get these queries to refresh? The queries aren't refreshing every hour/day. 

From my original post:

"I have the the Wrike data connected to Power BI but unfortunately my method doesn't allow for automatic refreshing of the data."

Unfortunately this hasn't changed as far as I know.

 

If you are able to get the automatic refresh working, keep the following information from Wrike's documentation in mind: "BI Export data is automatically updated once a day. You can use API to check if new data is ready for export."

https://help.wrike.com/hc/en-us/articles/360019094414-BI-Export

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Jacob Wright  Let me also share with you what my solution was to this issue.

I setup the Wrike Backup Tool to download the BI Export daily. I then created an SSIS job that runs shortly thereafter and moves all the .csv files into an SQL Server database. I then create my Power BI reports using that data source and am able to successfully refresh every day on our report server.

I realize that this is not the most elegant solution but it works for us since we don't have a need for real-time data.

Hopefully this will help.

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Seth Neds I was able to extract all the data. Thanks a lot.

How did you schedule the BI export daily. I am not able to find much documentation on backup tool. How can we define the location and schedule of backup?

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Data Feed

We just used a Windows Task Scheduler that runs the Wrike export daily. I believe the export is placed in the same folder/location as the java application.

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Seth Neds Thank you. This helped.🙂

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Seth Neds

I believe that the reason a scheduled refresh will not work is because the URL of the "https://storage.www.wrike.com/data_export/resource/123456789?accountId=xxxxxxxxx" will always be dynamic in nature. This is because the file number following the /resource is updated daily & expires after 7 days.

Chris Webb wrote this great blog post on how to get around schedule refresh issues that occur here:
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

However this will continue to not be possible until the Wrike team allow a generic file path to be return as a valid URL

ie."https://storage.www.wrike.com/data_export/resource?accountId=xxxxxxxxx" would be ideal. If this path were valid then we would be able to enable schedule refresh.

1
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hi Yasmeen Wilde, I hope you help me with exporting the data from wrike and put it into SQL server.

where should I start from?

Thanks!

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hi george murad, welcome to the Community! 👋

I can see that our Support team has reached out to you about this 👍

Please let me know if you have any other questions!

Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover

Lisa Wrike Team member Become a Wrike expert with Wrike Discover

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

All,

I'm trying to connect Wrike API with MS Excel (https://help.wrike.com/hc/en-us/community/posts/115000493205-Connecting-Wrike-s-API-with-MS-Excel-and-Power-BI) but I have the same error.

Any idea how to solve this?

Thx

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hey Chiara Gallo, I can see you've already discussed this with our Support team 👍 Please feel free to share the solution here 🙂

Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover

Lisa Wrike Team member Become a Wrike expert with Wrike Discover

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Hello Seth Neds, were you able to solve the refresh problem? because when I publish the solution to Power bi service the url doesnt refresh

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos
Avatar
Juan

Hi bassem riachi, welcome to the Community! Thank you for your comment, I have raised a ticket with our Support Team, and one of our experts will contact you shortly to assist you with your case.

In the meantime, feel free to check our New to Community forum to discover all the resources you can find in our Community 🙂

0
👍 Spot On 💡 Innovative Approach 💪 Stellar Advice ✅ Solved 🪄 Remove Kudos

Folllowing List for Post: BI export: Wrike connection problems
[this list is visible for admins and agents only]

Arriba
Didn’t find what you were looking for? Write new post