Forum Discussion
Grouping Data - Sample Attached
Hi all - I have a dataset, which has multiple lines and each line of data has a service provided. I need to produce a PBIX which allows me to show both the individual number of services we have offered, per session data (sess date) and also the number based on the groupings of the services offered. The attached link might shed some light on this. The data to the left is a cut down version of what I have - the data in yellow to the right of this is what I'd like to be able to also show.
Not sure I can do this in the same cohort of data or whether I'll need another table created but I need to show inidivual and groupings of services both in a table / matrix and graphically. Obviously the real dataset is much larger so I'll be showing this over a period of time.
I've also supplied some tarriffs which go with each service offered and, again, I need to be able to present this according to the grouped services per session we've offered.
Note the Full LV which is a different tarriff so need to be able to differentiate between individual tarriffs and this combined one when it's in the data.
Thank you!
Hi Creative_tree88 ,
Thank you for reaching out to Microsoft Community.To achieve a session-level summary view in Power BI, you can use Power Query to group and combine service data per session. The goal is to produce a clean table that shows one row per session (Cust No + Sess Date), with a Service Combo string, while also keeping key details like the Epi Number. Below are the steps, followed by how you can visualize this in Power BI.
Step 1: Group by Cust No and Sess Date
Open Power Query (Transform Data).
Select the Cust No and Sess Date columns.
Go to the Home tab and click Group By.
In the Group By window:
Group by: Cust No, Sess Date
New column name: AllData
Operation: All RowsStep 2: Create the Service Combo Column
Go to Add Column > Custom Column.
Name it Service Combo.
Use the following formula to combine all services in that session:powerquery
Text.Combine(List.Sort(Table.Column([AllData], "Service")), " + ")
This concatenates all service codes in sorted order, separated by " + ".
Step 3: Expand AllData Table
Click the expand icon on the AllData column.
Select only the Service and Epi Number columns to expand.
This brings those values back into your query, aligning each service with its session and Service Combo.Step 4: Close & Apply
Click Close & Apply to load your transformed data into Power BI.
Step 5: Create a Summary Table in Power BI
In the Data or Report view, create a new table visual (or use DAX if needed).
Add the following fields:Cust No, Sess Date, Epi Number (use First inorder to pick just one), Service Combo
Please refer to the sample pbix file attatched.
Best Regards,
Chaithra E.
6 Replies
Is this what you need?
On the tariffs, though, I did not get wat you are asking
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi Creative_tree88, is mine a solution? I ahve the file ready for you to share but please clarify about the tariffs so I complete my solution
Thanks
Hi Creative_tree88 ,
Thank you for reaching out to Microsoft Community.To achieve a session-level summary view in Power BI, you can use Power Query to group and combine service data per session. The goal is to produce a clean table that shows one row per session (Cust No + Sess Date), with a Service Combo string, while also keeping key details like the Epi Number. Below are the steps, followed by how you can visualize this in Power BI.
Step 1: Group by Cust No and Sess Date
Open Power Query (Transform Data).
Select the Cust No and Sess Date columns.
Go to the Home tab and click Group By.
In the Group By window:
Group by: Cust No, Sess Date
New column name: AllData
Operation: All RowsStep 2: Create the Service Combo Column
Go to Add Column > Custom Column.
Name it Service Combo.
Use the following formula to combine all services in that session:powerquery
Text.Combine(List.Sort(Table.Column([AllData], "Service")), " + ")
This concatenates all service codes in sorted order, separated by " + ".
Step 3: Expand AllData Table
Click the expand icon on the AllData column.
Select only the Service and Epi Number columns to expand.
This brings those values back into your query, aligning each service with its session and Service Combo.Step 4: Close & Apply
Click Close & Apply to load your transformed data into Power BI.
Step 5: Create a Summary Table in Power BI
In the Data or Report view, create a new table visual (or use DAX if needed).
Add the following fields:Cust No, Sess Date, Epi Number (use First inorder to pick just one), Service Combo
Please refer to the sample pbix file attatched.
Best Regards,
Chaithra E.Hi Creative_tree88 ,
We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.Hi Creative_tree88 ,
We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.
We truly appreciate your patience and look forward to assisting you further if needed.
Warm regards,
Chaithra E.Hi Creative_tree88 ,
We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.
We truly appreciate your patience and look forward to assisting you further if needed.
Warm regards,
Chaithra E.
