Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
thanks in advance!
Solved! Go to 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
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 🙂
@Sergii24 I have mentioned the problem in more details about, I hope now you will be able to understand the question.
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
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?
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |