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:   ID DATE SERVICE DESCRIPTION SALE PRICE 022 20-06-2015 POT123 3 HIJKL                    185.19 022 20-06-2015 PACK ABC PACKAGE         ...
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

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

  • Md_asgar's avatar
    Md_asgar
    Frequent Visitor
    2 years ago

    Hi Anonymous 

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      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