Groupng Table

[Md_asgar]

I have a sample data set in the below:

ID	DATE	SERVICE	DESCRIPTION	SALE PRICE
022	20-06-2015	POT123	3 HIJKL	185.19
022	20-06-2015	PACK	ABC PACKAGE	165.44
022	20-06-2015	POT123	CG KIT	977.40
022	20-06-2015	PHYSIO	PHYSIO HC 1	123.46
045	25-06-2015	WHEELS	ECHO	1,111.15
045	25-06-2015	POT123	3 HIJKL	222.23
045	25-06-2015	POT123	TOP - 123	136.84
045	25-06-2015	PACK	ABC PACKAGE	165.44
045	25-06-2015	AM	AMBULANCE	720.19
045	25-06-2015	FEES	A FEES	2,819.02
045	25-06-2015	WHEELS	B TESTS	190.13
045	25-06-2015	POT123	CG KIT	572.86
045	25-06-2015	PHYSIO	PHYSIO HC 1	141.98
045	25-06-2015	PHYSIO	PHYSIO HC 1	141.98
045	25-06-2015	PHYSIO	PHYSIO HC 1	141.98
045	25-06-2015	PHYSIO	PHYSIO HC 1	141.98
120	20-06-2015	POT123	123 ABC	0.72
120	20-06-2015	POT123	123 ABC	4.32
120	20-06-2015	POT123	3 HIJKL (10)	29.84
120	20-06-2015	POT123	CAP	3.29
120	20-06-2015	POT123	EXT 100	37.04
120	20-06-2015	POT123	TOP - 123	9.88
120	20-06-2015	PACK	ABC PACKAGE	230.46
120	20-06-2015	POT123	A INJ	35.60
120	20-06-2015	FEES	A FEES	1,749.03
120	20-06-2015	POT123	BHI TAB 1	0.40
124	26-06-2015	WHEELS	ECHO	1,222.26
124	26-06-2015	POT123	3 HIJKL	185.19
124	26-06-2015	PACK	ABC PACKAGE	165.44
124	26-06-2015	AM	AMBULANCE	720.19
124	26-06-2015	POT123	CG KIT	977.40
124	26-06-2015	PHYSIO	PHYSIO HC 1	135.81
124	26-06-2015	PHYSIO	PHYSIO HC 1	135.81

below is the output table:

DESCRIPTION	COUNT OF PATIENTS	NO. OF TIMES SERVICE WAS AVAILED	REMARKS
ABC PACKAGE	2	2	Patient ID 278 has used it twice. 423 has also used it twice  - see table below for explanation
ABC PACKAGE	11	1	All other patients were given the service only once
Patient IDs for ABC Package
022
045
120
124
156
200
278
278
317
370
410
423
423
425
460

Here is the explanation:

there is a Description column having one description as "ABC PACKAGE", it's total rows  count is 15, and out of these 15, 2 patient IDs have 2 times duplicate each, so total duplicate row count becomes 2*2 =4  and the remaining 11 are unique, And in the table, one rows for the "ABC Package" shows how many patient ID have duplicate rows(i.e 2) and the 2nd row for the same description "ABC PACKAGE" have unique rows(i.e 11),

This I wanted to achieve in the Output table.

thanks in advance!

[Sergii24]

Hi @Md_asgar, unfortunately I don't uderstand your quesiton... You've posted an input, which is good (please make sure to paste it as a table so person who help you can easily copy it for testing).

Now, having that input, what do you want to achieve? try to make it clear step-by-step. If you have formulas in Excel, please explain them in details. After that we might be able to help you 🙂 

[Md_asgar]

@Sergii24 I have mentioned the problem in more details about, I hope now you will be able to understand the question.

[v-cgao-msft]

Hi @Md_asgar ,

Reference the source table in PowerQuery, then filter out all 'ABC PACKAGE' records, then group by [ID],[DESCRIPTION] to calculate count rows, and finally group by [DESCRIPTION],[COUNT OF PATIENTS] to calculate count rows.

let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(Source, each ([DESCRIPTION] = "ABC PACKAGE")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID", "DESCRIPTION"}, {{"COUNT OF PATIENTS", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"DESCRIPTION", "COUNT OF PATIENTS"}, {{"NO. OF TIMES SERVICE WAS AVAILED", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows1"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Md_asgar]

Hi @v-cgao-msft 

Thanks for the solution, but the solution which you gave is static, suppose I have more than 50 descriptions and I want to see all descriptions in the same way, which means whatever the number descriptions I want to see for all like this. So, in this case, how to do it?

[v-cgao-msft · Accepted solution]

Hi @Md_asgar ,

You can remove the filtered records from the first step.

let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"ID", "DESCRIPTION"}, {{"COUNT OF PATIENTS", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"DESCRIPTION", "COUNT OF PATIENTS"}, {{"NO. OF TIMES SERVICE WAS AVAILED", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"DESCRIPTION", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group