Grouping data from multiple tables

Hi Creative_tree88 ,

To achieve your goal of counting distinct combinations of procedure codes per session (sess_code), the most efficient way is to use Power Query. You can group the data by session and then concatenate the related procedure codes into a single string. This allows you to create a clean summary table that reflects how many times each unique combination of procedures occurred.

Start by merging your Appointment Table and Procedure Table if you haven’t already. Once merged, go to the Power Query Editor and use the Group By feature. Group the table by sess_code and add a new column called AllProcedures with the operation set to "All Rows".

After grouping, add a custom column to concatenate the sorted list of procedure codes for each session. This ensures combinations like "E921 + E931" and "E931 + E921" are treated as the same. Use the following formula in a custom column:

Text.Combine(List.Sort(List.Transform([AllProcedures][procedure code], Text.From)), " + ")

Next, remove unnecessary columns and retain only the sess_code and your new concatenated procedure string column. Then group this new table again, this time by the concatenated procedure string. Use the operation "Count Rows" to get the number of times each combination appears. The final output will match the second table in your image, where combinations like "E921 + E931" or "E921 + E931 + E940" are counted per session.

This approach avoids the need to unpivot and works seamlessly even when working with multiple linked tables from different sources. If you prefer to use DAX instead, I can help write a calculated table to achieve the same result, although Power Query is more straightforward for this scenario.

Best regards,