Stuck on Automating using SharePoint

Nilsen Septon

Scenario = ‘Gift’ dashboard created in Tableau using a data file based on an export from RE. The data file that Tableau is pulling from resides in SharePoint.

Issue = Removing any one individual workstation from the equation - putting everything in the cloud so that the process works all the time regardless if my machine dies or whatever scenario might happen

Steps taken so far

1. Export created and scheduling through Queue - this works just fine
2. Using WinSCP to pull the file from RE Hosted to location of my choice - this works just fine
3. Using PowerQuery to manipulate the RE Export - this works just fine
4. Manually refreshing the file (to invoke the query) - this works just fine
5. Manually copying with ‘replace’ option the file Tableau is pulling from - this works just fine
6. Automating the manual steps above…..that's where I'm stuck, and more so, with the file that Tableau is reading from

Steps tried…

• First iteration - this works but it's all manual
  • Store export file on local drive
  • Store file transformed through PowerQuery on local drive and then refresh as needed
  • Copy w/replace transformed file from local drive to sharepoint folder for Tableau
• 2nd iteration - process fails
  • Store export file in sharepoint
  • Store file transformed through PowerQuery in sharepoint but need to open in desktop view for excel to refresh
  • Copy w/replace transformed file from sharepoint folder over other sharepoint folder that Tableau is reading from
    • This breaks because for whatever reason, Tableau sees this as a new file…seems that sharepoint renames the file somehow??
• 3rd iteration - process fails
  • Store export file in sharepoint
  • Store file transformed through PowerQuery as the file that Tableau uses
  • Refresh the transformed file (remember, this resides in sharepoint) but comes up with the following error "Couldn't refresh data - This workbook contains Power Query queries using data sources that can't be refreshed in Excel for the Web
    • Apparently, if I opened up the file in desktop view, refreshed and then saved, this it would probably work….but again, I want to take out any manual intervention

While I'm still thinking of what options I might have, how have any of you automated using sharepoint and bypassing any manual intervention and bypassing any local machine.

Alex Wong

@Nilsen Septon
I don't use Tablueau so I don't know if you can do this or not.

In order for you to fully automate and hands off on any manual process, everything must be done via cloud storage, which in your case, it is SharePoint file location. So the first thing you need to confirm is, can Tablueau “source” from a SharePoint file location, NOT a local file location.

I work with Power BI, and Power BI can easily “source” from a SharePoint file location, and once published to Power BI service (basically making the report “live” on the cloud platform of Power BI), I can schedule automatic refreshes of the Power BI data source. (meaning getting of new data from SharePoint file location).

Looking at your process, you are using PowerQuery to transform the data into what Tableau can “consume”. Does Tableau not have a data transformation functionalities? If it does, you should use that instead of PowerQuery. In my mind, here's what your process should look like:

• RE:Queue schedule data export (whatever time interval you set)
• Use a workflow automator (such as Microsoft's PowerAutomate), which can be triggered on SFTP File Modified. If other automator doesn't have a trigger on SFTP file modified, you can have the workflow trigger at timed interval knowingly after the RE:Queue is done
  • in the workflow, have the file (probably a csv file) copied to the SharePoint file location and name it explicitly the same name that will be used by Tableau directly
  • if Tableau doesn't work with csv well, there are multiple ways you can change the csv to an actual excel (xlsx file extention). I have a few post in community about this you can search on
• From Tableau, target the source to the SharePoint file location (csv or xlsx), and do the transformation of data in Tableau.
• Use in Tableau visualization

Nilsen Septon

@Alex Wong
Thanks again Alex - I appreciate your help and assistance!

Tableau is able to source from a SharePoint file location…the main problem I'm having is refreshing the file. I'll look to see if Tableau has a transformation function and if it does, it would make sense for Tableau to refresh the source much like you are having Power BI do it. Basically, I am able to do everything you describe with the exception of automatically refreshing the data source which resides in a SharePoint location.

I'll try a few things as you have triggered some additional thoughts. In truth, I initially wanted to do all of this through PowerQuery and Power BI, but I was overridden and was told we'd be using Tableau…not that Tableau is bad, it's just working more with the known vs the unknown.

Thanks again - I'll update here if I find a resolution. If anyone else has a thought or two, I'd love to hear them.

Nilsen

Dan Snyder

@Nilsen Septon I think what @Alex Wong wrote is your best approach. I used Tableau several years ago briefly and I think as long as you have the access to transform your data before publishing it for others to see Alex's approach will work perfectly.