Forum Discussion
Matrix with text and numbers
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
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!
Hi MauricioHV
Just following up on your question about handling mixed data types (text and numbers) in the unpivoted "Value" column of your matrix.
As mentioned earlier by danextian Ashish_Mathur , Power BI doesn’t allow a single column to store both text and numeric types while preserving number formatting for calculations. The recommended approach is to:
-
Split the column into separate fields—one for numeric values (for calculations) and another for text (like laboratory names).
-
Use conditional logic or DAX formatting to handle display needs while preserving numeric integrity for calculations.
Let us know if you’ve had a chance to apply this solution or if the issue still persists we're happy to assist further if needed!
If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.
Thank you for being part of the Microsoft Fabric Community!