Forum Discussion
Matrix with text and numbers
I have a Matrix like this.
The original table was unpivot, so the plot values are coming for the final unpivot column "Value".
However, in this column I have mixed text and numbers (the laboratory and the value itself).
I want to mantain the format as number in order to ease future calculus, however, the text don't allow that.
Is there any way I can have both formats? Text and numbers?
Thanks and greetings to everyone
Hi MauricioHV
You cannot maintain the number format of a column if any of its value is non-number. Number + Number = Number while Number + Text =Text. What you can do is create a custom column in the query editor that returns only the rows that are numbers. Example
// Attempt to convert [text_number column] to a number; return null if conversion fails try Number.From([text_number column]) otherwise null
6 Replies
Power BI, you cannot store both text and numbers in the same column as numeric type — a column is either text or numeric, not both.
Hi,
In Power Query, Laboratory should be a seperate column.
Hi MauricioHV
You cannot maintain the number format of a column if any of its value is non-number. Number + Number = Number while Number + Text =Text. What you can do is create a custom column in the query editor that returns only the rows that are numbers. Example
// Attempt to convert [text_number column] to a number; return null if conversion fails try Number.From([text_number column]) otherwise nullHi MauricioHV
Just wanted to follow up on your question regarding the matrix visual, where your unpivoted "Value" column contains a mix of text and numbers which is affecting formatting and future calculations.
The community has danextian Ashish_Mathur already shared a few suggestions to help separate or manage the mixed data types. If you’ve had a chance to try those solutions, it would be great to hear how it’s working for you!
If you're still facing issues with keeping number formatting intact while preserving textual values, feel free to share more details about your data structure we’re happy to dive deeper and assist further.
Looking forward to your update!
Hi MauricioHV
Just checking in on your matrix visual issue where your "Value" column mixes numbers and text, causing formatting trouble and making calculations tricky.
Community experts like danextian Ashish_Mathur have already offered some suggestions to help separate the data types. If you've tried them, we'd love to hear how things are going!
If the problem persists and number formatting is still getting lost alongside textual values, feel free to provide more details about your data setup we're here to dive deeper and help out.
Looking forward to hearing back from you!
