| Excerpt | ||
|---|---|---|
| ||
connect an Excel workbook to Projector data |
| Info |
|---|
This help page teaches you how to connect an Excel workbook to Projector report data. It will also show you how to edit and maintain the data. |
Create Report
You'll first need to create a report in Projector so that you have data to pull into Excel.
- Go to your Reports tab in Projector
- Create or edit a report. On the Output tab set the following - Web service code and Auth token. If you don't see the auth token option, visit Account Settings Editor.
- Run the report
- Right click the report in the output area and choose Advanced | Show Output URL. ClickCopy CSV URL.
Create Query from Web
| Warning |
|---|
Don't use the From Web button found in the default toolbar, you have to go through the New Query dropdown. Yay for confusing User Interfaces! |
Follow these steps to connect Excel to the Projector report. This section assumes you have your CSV URL already.
- Open an Excel workbook
- Go to Data menu | Get & Transform ribbon section | New Query dropdown
- Choose From Other Sources | From Web
- Paste the URL into the From Web box. Click OK.
- Your data will preview in a popup window. You should be able to leave all defaults. Click Load.
- The data is inserted into your spreadsheet.
...
Refresh
You can schedule your Projector reports to run on a schedule and the Excel spreadsheet to refresh with the new data.
...
- From the Data menu | Connections ribbon section | Choose Connections
- Select a connection and click Properties
- Set your refresh options
Add or Remove Columns
If you alter the Projector report by adding or removing columns, you should make sure you update your Excel spreadsheet too.
- If you are adding a column, make sure the column has already been added to your table in Excel
- From the Data menu | Get & Transform ribbon section | Choose Show Queries
- A sidebar appears with your queries
- Right click your query and choose Edit
- Click the Right click your query and choose Advanced Editor button
On the second line, edit Columns and set the proper number of columns.
Code Block Source = Csv.Document(Web.Contents("https://app.projectorpsa.com/report/code/mycode?format=csv&UserName=tom@revcorp.tcs&AccountCode=revcorp-tcs&AuthToken=abc"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),On the third line, edit to remove columns that will no longer be imported or add columns that will be imported. You may need to get the column name from the Projector report. Make sure you set the correct column type (text, datetime, Int64.Type, etc).
Code Block #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type datetime}, {"Engagement Code", type text}, {"Project Code", type text}, {"Resource Display Name", type text}, {"Person Hours", Int64.Type}, {"Data Type", type text}})Click Done
Click Close & Load
...