Grouping data from multiple tables

[Creative_tree88]

Hi all - I have had to import around 4 different tables, from different databases, into Power Query - linked them together, to be able to produce a lovely table which displays it perfectly.

However, I'd now like to comine different rows into the same row using a unique key found in the tables.

I've tried to illustrate my issue in the diagrams below.  The top diagram illustrates how the data 'presents' itself at the moment.  I can count each procedure code and produce a nice chart to show this.  However, I'd ALSO like to be able to count the unique combinations of each procedure PER session code (sess_code) which would yield results like the table at the bottom.  See how the count is looking at each unique session code but grouping the procedures together on one line.

Usually this could be done in Power Query by un-pivoting, etc.  However, because I'm dealing with multiple tables I cannot do this.  Is there a clever measure I can employ to achieve this?

[v-priyankata]

Hi @Creative_tree88 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Creative_tree88 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Creative_tree88 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-priyankata]

Hi @Creative_tree88 
Thanks for your detailed explanation and diagram it was very helpful.

I’ve reproduced your scenario in Power BI Desktop using the multiple tables and relationships as you described. After merging the tables and grouping the procedures by session (sess_code), I was able to generate the combined procedure codes and names per session exactly as shown in your expected output.

To help you understand and reuse the solution, I’ve attached a sample .pbix file with all the steps applied including:

• Table merging (via Merge Queries)
• Grouping (using Group By → All Rows)
• Custom columns for code and name combination
• Final summary visual counting each combination

Output:

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[DataNinja777]

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,

[Creative_tree88]

@DataNinja777 Could you possibly send me the DAX which you mentioned as I don't seem to be having any luck at all with your initial suggestion.  Many thanks indeed.

[Creative_tree88]

@DataNinja777 Many thanks.  I've managed to get the combo working for procedure code (E921 etc) but I need to now also convert these codes into their respective names...

E932 = Spirometry

E921 = GTF

E935 = LV

E928 = FENO

I've tried doing this by adding a conditional column in Power Query, but then I cannot seem to use the following formula..keeps coming up as error?

The new conditional column is called 'Procedure Name' .  Not sure what I'm doing wrong!

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