Errors in PowerBI Calculations

srsubs

Firstly, very impressive work and I look forward to using actively, thank you. One of the early errors I'm seeing in using PowerBI is the following:

Error Message:

MdxScript(Model) (12, 187) Calculation error in measure 'Report'[Equity Value]: A table of multiple values was supplied where a single value was expected.

Stack Trace:

Invocation Stack Trace:

Activity ID
91aa84ec-34e7-4820-9b84-0e762da2fde4

Time
Sun Aug 21 2016 03:01:39 GMT+0800 (Malay Peninsula Standard Time)

Version
2.37.4464.602 (PBIDesktop)

Error Code
rsDataShapeProcessingError

OData Error Message
Failed to execute the query.

Also, in PowerBI, the default view begins in Original Currency mode, which has quite a number of errors, including blanks for several views with the above error. When I switch to say USD, a few more fields/views are populated but still quite deficient.

Furthermore, I'm using some sample data that is resulting in different results on my local Excel vs that seen through PowerBI, eg. in dividends calculated -- in Power BI, one of the transactions is being omitted even though it is in the current year. Also, in both the versions there are other errors, eg. in the start value of the portfolio for the most recent month. Perhaps it would be easier If I shared my sample file and those for dates.csv, quotes.csv etc for you to examine?

Many thanks...Sam

VidasMatelis

Sam,

I just started my vacation, I will be back Aug 27th. I will see if I can look into your problem before.

srsubs

Thank you Vidas...please enjoy your vacation - after return is fine.

VidasMatelis

Hi,

Files you attached are comma separated, you will not be able to refresh data, so first thing you need to focus on that.

1. Make sure your psConfig.txt file has parameter values set to create tab separated file.
2. Check parameter for location where you .CSV files are going to be created. If parameter value is empty, location is relative to script path. I had cases when users had older version scripts that were pointing to c:\portfolioslicer\psdata , but new version scripts has no folder set, so just old version CSV files were picked up by excel. Check CSV file creation date to confirm it is fresh.
3. If above does not help, possibility is that script ps1 files are not the latest. Redownload.

srsubs

Thanks for your comments. Some questions:

1. CSV stands for comma separated values... This is causing some confusion because of the requirement for tab-separated but your documentation refers to csv. When I save the files from Excel, I have been creating the data fields using tabs but choosing csv while saving. Should I be choosing text files (tab separated)?
2. I am using Portfolio Slicer for PowerBI. I cant find the psConfig file and have not used the scripts at all. I prefer to create the necessary quotes and CurrencyConv etc files from a worksheet.

Many thanks...

srsubs

Also, no matter what manner of file saving I choose (and I have checked that the data is tab-separated in my text file), when I try to load the data I keep getting the error that it cannot find the field CurrencyFrom.

VidasMatelis

File extension is CSV, but inside you must have tab separated values. Inside each file data must be in specific format - you must have first line with column headers.
Could you please download scripts and run them once and check generated files. I will add more documentation to clarify this some time later.

srsubs

Other than for the file format I actually have no need for the script route. Any chance you could provide any existing sample file you have which is in the correct format but in which users can simply populate their own data and save in a recommended way? I don't mean to shift the burden to you but have spent hours and hours trying to get this right, followed every tip on the site, got all the headers, tab seps, everything, and something simple is still eluding me. I will of course try the scripts if there's no other option but suspect it will come with it's own bit of complications. In any case, many thanks.

srsubs

Ok, pleased to report I found a solution to the file format issue. Even if a file is created in Excel and saved as .csv (which by default will insert commas), the workaround is the following which I found on another forum:

Open notepad, press TAB, select and copy the tab space. Open the CSV in notepad, Edit->Replace... fill , in the first box and paste your tab in the second box and just Replace All.

Using this, and the script to check files, I finally got the external data files to clear all requirements.

Onwards.