Power Query problems

[Zuv Kui]

Hi, I wonder if you could help me.

I have a table from which I have created a power query. The result of the query (sorted by fruit and then seller) is e.g.

Fruit	Date	Seller
Apple	28/02/2018	Ann
Apple	01/03/2018	Ann
Apple	01/03/2018	Bev
Banana	25/02/2018	Ann
Banana	02/03/2018	Bev

I want to use the query as a dynamic report, and have the total count of the different fruits stand out. I would like the query to look like:

Fruit	Total	Date	Seller
Apples	3
Apple		28/02/2018	Ann
Apple		01/03/2018	Ann
Apple		01/03/2018	Bev
Bananas	2
Banana		25/02/2018	Ann
Banana		02/03/2018	Bev

The 2 problems I am having are:

1. Whilst I can use a count formula =COUNTIF(A2:A9, "Apple”) in the Total column (cell B2), Excel does not like the formula =COUNTIF(A2:A9, "Banana") in cell B7. Is there a better formula to use?

2. When I refresh the report, it all messes up. E.g.

Fruit	Totals	Date	Seller
	3
	3
	3
	3
	3
	3
	3
Apple	3	28/02/2018	Ann
Apple	2	01/03/2018	Ann
Apple	1	01/03/2018	Bev
Banana	0	25/02/2018	Ann
Banana	0	02/03/2018	Bev

Is there anyway to resolve this?

Many thanks in advance,

Zuv

 

[Bill Szysz]

Hi Zuv
Check, if is that what you want.

Regards

 

[Zuv Kui]

Hi Bill,

Yes, this is exactly what I want. Thank you. Could you provide step-by-step instructions though as I can't get the conditional formatting to work.

Many thanks, Zuv​

 

[Rudi]

Yes, this is exactly what I want. Thank you. Could you provide step-by-step instructions though as I can't get the conditional formatting to work.
​

Hi Zuv,

Here are the steps (if I may chip in Bill )...

​

1. 
   [*=left]Select F3:I15 (all the records in the table output)
   [*=left]Activate C/F from the Home ribbon
   [*=left]Select "Use a formula to determine which cells to format"
   [*=left]In the text box enter the formula: =$G3<>"" (it tests if cell G3 has content)
   [*=left]Click the Format button and specify your formatting (eg: Bold and a fill colour)
   [*=left]Choose OK to confirm

Notes:
​

• 
  [*=left]Whereever there is a value in the G column, the condition is TRUE and the formatting is applied.
  [*=left]It is important to have the "$" sign in front of the column reference "$G3" in the formula.
  [*=left]The formatting automatically applies to the whole row as you selected the entire width of the table to start with.