Matrix with text and numbers

[MauricioHV]

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

[danextian · Accepted solution]

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

[v-aatheeque]

Hi @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!

[v-aatheeque]

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!

[v-aatheeque]

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!

[Ashish_Mathur]

Hi,

In Power Query, Laboratory should be a seperate column.

[Chanty4u]

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.