Forum Discussion

Md_asgar's avatar
Md_asgar
Frequent Visitor
2 years ago
Solved

Groupng Table

I have a sample data set in the below:

 

IDDATESERVICEDESCRIPTIONSALE PRICE
02220-06-2015POT1233 HIJKL                   185.19
02220-06-2015PACKABC PACKAGE                   165.44
02220-06-2015POT123CG KIT                   977.40
02220-06-2015PHYSIOPHYSIO HC 1                   123.46
04525-06-2015WHEELSECHO                1,111.15
04525-06-2015POT1233 HIJKL                   222.23
04525-06-2015POT123TOP - 123                   136.84
04525-06-2015PACKABC PACKAGE                   165.44
04525-06-2015AMAMBULANCE                    720.19
04525-06-2015FEESA FEES                2,819.02
04525-06-2015WHEELSB TESTS                   190.13
04525-06-2015POT123CG KIT                   572.86
04525-06-2015PHYSIOPHYSIO HC 1                   141.98
04525-06-2015PHYSIOPHYSIO HC 1                   141.98
04525-06-2015PHYSIOPHYSIO HC 1                   141.98
04525-06-2015PHYSIOPHYSIO HC 1                   141.98
12020-06-2015POT123123 ABC                       0.72
12020-06-2015POT123123 ABC                       4.32
12020-06-2015POT1233 HIJKL (10)                     29.84
12020-06-2015POT123CAP                       3.29
12020-06-2015POT123EXT 100                     37.04
12020-06-2015POT123TOP - 123                       9.88
12020-06-2015PACKABC PACKAGE                   230.46
12020-06-2015POT123A INJ                     35.60
12020-06-2015FEESA FEES                1,749.03
12020-06-2015POT123BHI TAB 1                       0.40
12426-06-2015WHEELSECHO                1,222.26
12426-06-2015POT1233 HIJKL                   185.19
12426-06-2015PACKABC PACKAGE                   165.44
12426-06-2015AMAMBULANCE                    720.19
12426-06-2015POT123CG KIT                   977.40
12426-06-2015PHYSIOPHYSIO HC 1                   135.81
12426-06-2015PHYSIOPHYSIO HC 1                   135.81

 


below is the output table:

DESCRIPTIONCOUNT OF PATIENTSNO. OF TIMES SERVICE WAS AVAILEDREMARKS 
ABC PACKAGE22Patient ID 278 has used it twice. 423 has also used it twice  - see table below for explanation 
ABC PACKAGE111All 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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

5 Replies

  • Sergii24's avatar
    Sergii24
    Super User
    2 years ago

    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's avatar
      Md_asgar
      Frequent Visitor
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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