Help removing duplicate headers in power bi table

[KarenGrube]

I've asked a similar question to this which was resolved by using a Matrix in Power Bi.  However, in that case, instead of Invoice Number, there was a text column.  In this case, the Matrix seems to think that the Invoice Number is a numeric column and won't add it next to the Invoice Date.  The Invoice Number is a text column, but the Matrix keeps treating it like a number. I can put the Invoice Date in next to the Vendor Name in a Matrix, but I can't place the Invoice Number next to the Invoice Date.  

I've tried setting this up as a Table rather than a Matrix, but the vendor name is duplicated for each row and I don't see how to show the Vendor name only once on the first row for each vendor in a table as you see below.

VENDOR    Invoice Dt.  Invoice Number     Amount

FedEx     01/23/2023   17634             $285.34

          02/12/2023   127634873         $374.12

Microsoft 02/03/2023   96778562          $672.98

Netsuite  04/12/2023   23090989264876    $187.23

          04/12/2023   176248760         $821.23

Samsung   05/05/2023   937627            $921.76

Any suggestions?  Please help.

[KarenGrube · Accepted solution]

The answer was to use a matrix, move the text "columns" to Rows, remove the +/- sumbol, then click on the icon that will expand all levels.  It works perfectly!

[KarenGrube]

None of the suggestions seem to work.  I'm not sure what I'm going to do. It looks as though using tables forces you to show duplicate row headers and a Matrix won't see a numeric text string as text.  I hope we can come up with something.  I even tried putting a space in front of the number and the matrix still looked at is as a number.  This seems like a bad flaw in Power BI.  The funny thing is that probably 25% of the invoice numbers are alpha-numeric, and it still won't treat them as text.

[CoreyP]

Can you take a screenshot of how you're adding this to the matrix, as well as the place(s) you changed the data type? I don't think this is a flaw in power bi. I've never not been able to change a field to text. 

[KarenGrube]

It already IS text.  Unquestionably.  It is a varchar field in the database and it shows as Text in the BI Query.  It's like Excel does with using the first set of rows to determine the data type, and unless you tell Excel not to do that, it interprets a number like an Invoice number as a number, even if 20 rows later they are all alphanumeric, and probably a third of the invoice numbers in the data are alphanumeric.

I changed the data type to Text even though the BI query already knows it's text and this is what I get: No matter what I do in a matrix. the invoice numbers go across the top as though there were column headers.

This doesn't work.  But what ALSO doesn't work is the fact that, if I make this a Table, the vendor names are duplicated for each invoice.  I guess my users will have to get used to seeing the vendor names duplicated, but that's a shame.

Any other suggestions?  I'd really appreciate being able to resolve this.

[KarenGrube]

The other option is to figure out a way to remove the duplicate row headers - Vendor Names - in the Table.  That would be my preference, but perhaps there is no way.  How odd.

[CoreyP]

Try changing the data type to text in Power Query. There should be nothing preventing that column from being changed to text. 

[christinepayton]

The columns in a matrix aren't meant to display text values like that - they either add column headers (e.g. years across the top) or summarize values (e.g. sum of amount). You might be able to mimic it by making a measure that takes the min or max of the invoice number if you put that into values, though. There will only ever be one value, so it's kind of a dummy measure, but it wants to be doing "math" in the values well regardless. 

[KarenGrube]

That's an interesting suggestion.  The Invoice numbers are never duplicated.  I can try that, though it seems like a lot of effort to get this to work.

[christinepayton]

You don't even really have to create a measure per se... I think if you drag in the invoice number into "values" it'll probably auto-summarize with a count or sum unless you have summarization disabled. You can click the field in the visual pane to swap it to a different summarization. 

[KarenGrube]

It defaulted to "First" summarization, which didn't work.

[CoreyP]

Change the data type of the invoice number column to text.

[KarenGrube]

I tried that.  It didn't work.  It shows as Text when I look at the data pane.