Grouped Tables - Merged. but creating duplicate rows.

[Anonymous]

Hello,

I have two grouped tables that i have merged together into one.  Both tables have different row counts.  When i merge them together, matching on a patient health card number it is creating duplicates in one of the grouped tables.  See pictures below.

The highlighted column is the one that is duplicating.  I only want to count the highlighted column once, compared to the right hand side.  It is duplicating because of how many more dates (visits) are on the right.  

Any help would be greatly appreciated! or maybe there is an alternative solution and merging is not the answer!

[OktayPamuk80]

Hi,

The issue is that a patient can come to outpatient visit couple of times and may come to emergency visit one or many times. It would be good to bring them over a case Id together (the Id that one HCN receives when coming to hospital. Another suggestion is not to merge them but append the to visit:

HCN	Type	Date In	Date Out
1	outpatient	2024/10/27	2024/10/27
1	emergency	2024/10/28	2024/10/29
2	outpatient	2024/09/2024	2024/09/2024

Of course, the outpatient records will always have the same date in and date out value however, by this you can plot them more easily. The additional case Id, would make it easier to identify, whether the different visits correlate to each other.

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[Anonymous]

Hi Oktay,

Thank you for all of your input.  We did the Unique Identifying Case ID in excel yesterday (very manualy).  We are anticipating to have to update this data at some point.  Do you think assigning the Case ID in excel is the way to go and then upload to Power BI or is there a way/formula to assign a Case ID to a specific Number (HCN)? 

Thanks,

Denise

[OktayPamuk80]

Hi,

This is strange. In my opinion it should be in the process of the emergency visit, to determine, that based on outpatient visit, the emergency visit emerged. Can't you have this implemented in the ERP system in the emergency room system? for the same patient you open a new case anyway and you need to select from previous cases, that it is still the same case (or relating to the existing one). By this, you can have multiple facts table including common master data (patient, calendar, etc.)

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

Regards,

Oktay

[Anonymous]

Hi @Anonymous ,

Based on the description, selecting the Left outer when merging two tables.

You can also view the following document to learn more information.

Left outer join - Power Query | Microsoft Learn

Merge queries overview - Power Query | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OktayPamuk80]

Hi,

I suppose in your example, one of your tables is the HCN and registration date (patient master table) and the other one is about the visits. The visits table is the facts table and I advice you not to merge them. Leave them as single tables and connect them in the model view. If however, you need specific fields then merge only requiring fields to make sure the HCN master table does not get duplicate records. What are you trying to achieve? What is the reason for merge (keep in mind, Star-schema is still the best data model option for Power BI).

Regards,
Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[Anonymous]

Also, if i leave them as individual tables and try to connect by HCN i am getting errors because neither are unique and there are duplicates.. hence why i tried merging them

[Anonymous]

Hi Oktay,

Thanks for getting back to me!  I did try the Left Outer Join with the merge with no success.. leaving them as single tables.  We are trying to look at the outpatient visits that followed with an Emergency Visit.  The unfortunate part is that the Emerg Reg Dates will differ from the outpatient visits Reg Dates and the only commonality to tie them together is the HCN.  We are looking to see how many days after an outpatient visit it took them to go to the Emergency Room.    We will have lots of outpatient visits that follow an Emergency Visit.  Any other advice would be greatly appreciated! I feel I am stuck!