How to change connections in Portfolio Slicer for Power BI Desktop model
Portfolio Slicer for Power BI Desktop file contains 14 tables:
- Tables "Account", "Allocation", "Config", "ReportCurrency", "Symbol", "SymbolSector", "Trans" and "TransType" - are loaded from Portfolio Slicer Excel workbook. This could be Excel 2010 or 2013 or 2016 workbook - it does not matter, it is just important that Portfolio Slicer Excel workbook would be from the same version as Portfolio Slicer for Power BI Desktop file.
- Tables "CurrencyConv", "Dates", "Dividends" and "Quotes" are loaded from corresponding external data files - same files that are used to load into Portfolio Slicer Excel workbook.
- Table "Report" is hardcoded table without records. It is used to group all calculations. There is no need to update connection for this table.
To change Portfolio Slicer for Power BI Desktop file connections follow these steps:
- First make sure that you already have installed "Power BI Desktop" (from now we will call it PBID) application. If no, you can install it from here. Please note that this application is updated monthly, so it is good idea to update your PBID application if it was installed more than a month ago.
- Start PBID, choose menu "File", "Open" and select Portfolio Slicer for Power BI file that you downloaded from our website. PBID will open by default in "Report" tab that will look something like this:
- From the "Home" menu choose option "Edit Queries". This will open "Query Editor" windows that will list all tables/queries on the left.
- From the left menu select table "Account" and then from right side "Applied Steps" area select first "Source" step, do right mouse click and choose "Edit settings" menu item. Click on "Browse" button and select your Portfolio Slicer Excel file. This should be the same file for all above tables.
Repeat this step for tables "Allocation", "Config", "ReportCurrency", "Symbol", "SymbolSector", "Trans" and "TransType" - all of these tables must point to the same Excel file.
- From the left menu select table "CurrencyConv" and then from right side "Applied Steps" area select first "Source" step, do right mouse click and choose "Edit settings" menu item. Make sure that configured file is pointing to location where you keep your external data files (default c:\PortfolioSlicer\PSData\ folder). If you need to change location, click on "Browse" button and select your CurrencyConv.csv file.
Repeat these steps for tables "Dates", "Dividends" and "Quotes", just make sure that you choose corresponding csv file.
- From the "Query Editor" window "Home" menu click on "Close & Apply" button. This will close existing window and will initial updating of all table and reports.
- In PBID application report view select each page and review slicers for that page. Each page will have "ReportCurrency" slicer - you probably will need to change selected value to your primary currency. Do this for each page.
- In PBID click on menu "File", then "Save" to save all changes you made.
- You are now ready to use Portfolio Slicer for Power BI Desktop. To refresh data make sure that your external data files are refreshed and then in PBID application click on "Refresh" button. As this application has just a few reports (comparing to 40+ in Excel), refresh time will take just 10-15sec. You can also easily add your own reports.
- You are not ready to publish your Portfolio Slicer data to PowerBI.com website and view reports online in any browser or using Phone/Tablet PowerBI application.