Cannot convert data type of a Sharepoint list column

[zallebban]

I am getting accustomed to Power BI Desktop after going through several tutorials and lessons from the Dashboard in a Day course.

I've successfully imported data from an online Sharepoint list.  I am trying to create a very simple visualization that shows quality of a deliverable (percentage) over time.

In Sharepoint, a series of yes/no questions -- each assigned its own column -- determine whether a deliverable met certain quality criteria.  I wrote a formula in Sharepoint to calculate a quality score based on those yes/no answers.  It computes as a 0-100 score and is marked as data type "number" in Sharepoint.

When importing the data into PBI, I can change the "date created" column to a "date" data type without a problem.  Trying to change the calculated score from "text" to "decimal number" (or any number) returns an error that the column can't be converted.  In case this was due to it being a calculated value, I created a new column in Sharepoint and manually entered the calculated score in that column.  The same problem occurs.

As a result, I am getting "Count of" scores rather than the scores themselves in my visualization.  As simple as this is, there must be a simple answer.   What am I missing?

I appreciate the help.

[v-yuezhe-msft]

@zallebban,

Does your column contain text strings such as “N/A”, ”Blank” and so on? If you have such text strings, you would need to firstly replace them with numerical values(e.g. 0), then change the data type of the column to Number.

There is a similar thread for your reference.
http://community.powerbi.com/t5/Desktop/Cannot-convert-a-negative-text-to-decimal/m-p/78245

Regards,
Lydia

[zallebban]

Lydia,

There is indeed one cell that is empty, but contains no text strings.  To test, I replaced it with a zero and the manual column did indeed convert to numbers.

The problem this poses is that a zero throws off our visualization.  Because the business process behind this simply hasn't yet hit the point where quality would be measured, that value would be empty.  While most entries in this column would have quality scores, a blank value would be a regular occurrence in the data so I planned to eliminate blank values via Power BI filtering (which I did in the Filter panel to only show values when not blank, but that doesn't address the original data type problem).

Thanks for the link -- wasn't quite the same problem though.

[v-yuezhe-msft]

@zallebban,

I suspect that if the cell really contains blank value, could you please copy the values of the column to a table and share me the table?

I make a test to filter a column that only show numerical values(eliminate text values), then I am able to convert the data type of the column to whole number.


Regards,
Lydia

[zallebban]

Lydia -- I'll do that.  Just give me a day or two to get past a deadline and I'll send that over.  Thanks again.

[v-yuezhe-msft]

@zallebban,

When I copy data from your Excel to my Power BI Desktop, everything works well when I convert the data type of Quality Score to Decimal Number.

Regards,
Lydia

[zallebban]

Same with me.  But when I try to convert it as it is imported from the original data source, it will not allow me.  Don't know where to go from here.

[v-yuezhe-msft]

@zallebban,

Is there any possibility that you create a formula in Power BI Desktop instead to calculate score based on yes/no answer ?

Regards,
Lydia

[zallebban]

I think that's a good option at this point.  If you know of a good tutorial or reference, send it my way.  Thanks, Lydia!

[v-yuezhe-msft]

@zallebban,

You can share sample data and post expected result with detailed logic here so that we can provide op appropriate formula.

Regards,
Lydia

[zallebban]

Lydia -- just sent you the file.

Currently, I am calculating in SharePoint based upon Yes/No answers to quality inspection points.  If it is easier, I could change the answers to 100/0 or 1/0 to make averaging easier.  But if there is a way to keep Yes/No, that would be easier for consumers to use.

The goal is a simple average across all points for a single item (row), which will yield a quality score (%) that is then graphed against time.

A potential challenge is that Power BI will refresh data and pull in items that have not yet undergone quality review; these will have to be ignored until there is data in the Yes/No fields on the next refresh.

Hope that explains it.  Let me know if you need more clarity.  Thank you again.

[zallebban · Accepted solution]

Lydia -- looks like I found a solution.  Following this example, I extracted the data out of the "FieldValuesAsText" column and removed the other columns.  From there, data type conversion was not a problem.

Thanks for your help and I hope this method can help someone else.

[v-yuezhe-msft]

@zallebban，

Thanks for sharing me the sample PBIX file. However, I am not able to access data in Query Editor, as I have no permission on your SharePoint Site. Could you please export data from the SharePoint to Excel and share me the Excel file?

Regards,
Lydia

[Greg_Deckler]

And that new column in SharePoint is a text column or a numeric column?

[zallebban]

It is designated as a number column.