Displaying two fields on same row

[satishgupta]

The table below is taking two rows per customer and is showing identical numbers on both rows. That's because it is showing totals per client Number (e.g., 1099) on one row and the same figures under client name (e.g., Client A).

My question: Is it possible for me to have only one line per client? Essentially, what I want to do is to have client number in column 1, client name in column 2, and dollars in next three columns, all on a single row.  Please note that there is a one-to-one relationship between client # and Client name. In other words, there will not be multiple client names associated with one client #.

Finally, I have seen solutions to this problem for Power BI. I am doing this in Power Pivot and, therefore, looking for a solution that works in Power Pivot.

Thanks

Satish

Client #	Client Name	July-18	August-18	September-18
1099		19,719	25,737	-24,259
	Client A	19,719	25,737	-24,259
1307		840	751	754
	Client B	840	751	754
1308		1,461	2,693	1,123
	Client C	1,461	2,693	1,123

[Anonymous]

Hi

Quick QN : When you say client # and client name are in 1:1 relation, How you are getting blank value in Client# and client name columns?

For row1, why client name is not displaying along with client# ?

Thanks

Raj

[satishgupta]

I thought blank cell is the norm when you prepare any pivot tables. They are set up to give a table based on one field. Whenever you put two fields in rows, it assumes a parent child relationship and starts with totals for the parent, followed by data for each child. In this case, it is assuming client # is the parent and Client name the child. That's why you see blank cells. Again, I think all pivot tables, including in plain Excel, would show results like this.

Satish

[Anonymous]

oh ok, sorry i didnt notice that this is pivoted data.

Can you show us how the data looks before you do pivot?

Thanks
Raj

[satishgupta]

Raj,

I should have made it clearer in my request.

Data files look like the one below. There are mutiple files, one per month. The Power Query combines all of them and that's what feeds the final Pivot Table that I have in my request. Thanks.

					Total
1-Aug	DR-1099	7138	Client A	1/1/1990	-81,670.58
1-Aug	DR-1307	7295	Client B	1/1/1990	739.26
1-Aug	DR-1308	7549	Client C	1/1/1990	2,681.58