How to set percentage when column from Excel contains both text and numbers

[LiamJohn]

Hi all,

I have scoured the forums and am surprised I can't find someone with a similar hoop to jump through. I have a basic gallery on a few screens in my app and it is sourced from an excel file with two simple columns: a title and then a value. In the value columns, i have simple text responses (such as 'Yes') however in one of the rows I need to have a percentage (rather than just a decimal point number). I've found how to convert to percentage using the text function, but when doing this it applies it to all of the fields in my gallery, which of course I don't want. Any tips for a newbie would be much appreciated.

[RusselThomas]

Hi @LiamJohn ,

So first, I'm looking at why this is happening - Your challenge with everything changing is simply because your data model is pivoted 'sideways'.  Each row of data is actually only part of a virtual "Row" of two generic columns that spans multiple rows....In other words, each row cannot be considered a complete set of data unless it's taken in the context of the rows that follow onto it - your data schema is contained inside your data, which is why you're seeing this behaviour.

There may be a good reason for this, so I understand you might need to work around it, but you should understand that these issues tend to go away when your data is unpivoted and each field has it's own column.

As an example, in the following image, each Row represents a full set of data.

This makes it a lot easier to focus on a specific field in the context of the complete set of data.

When you place your model into a gallery, the gallery is treating each line as a full set of data - even though you're reading it as a subset of data that is made up of many rows.  From the gallery's perspective, anything you apply to the formatting of the "Value" field in a row, get's applied to all rows because there are only two fields in the set - Title and Value.

So to work around this (if you have to - if you can change your data model I'd strongly recommend it);

The rule that defines the formatting of the Value column, is based on what's inside the Title column.

Inside your Gallery, where the Value field is displayed, you set up your formatting on it's Text: property - something like this;

If(Title = "Percentage of Transactions", Text(Value(ThisItem.Value)*100, "###%"), ThisItem.Value)

(or whatever your text conversion function to percentage is you're using).  So basically saying "if the title is percentage of transactions, then value needs to be formatted, otherwise just put the value as it normally is"

Hope this helps,

RT

[LiamJohn]

Hey Russell,

Thanks so much for the response! This makes sense, I'll go ahead and play around with this today. 

Much appreciated