Many to Many Relationships with Common but Duplicate Key

#1

Hi Sam,

I have two tables one is a detailed accounting (fact table) with account no, group #, insurance codes, two flags columns and amount. The second Table (In a matrix format) is a lookup and includes insurance names (A thru J), insurance codes(Y thru 6) and %. The only common filed between the two tables is insurance codes that are duplicated in both Tables. I need to join the two tables to calculate various measures. In Excel this is done rather manually with sumproduct formula. I have tried left join in SQL and then connect final table to Power BI. I also tried joining the two tables in Power BI with many to many relationships with no avail. Enclosed are images of the two Tables. As you can see even account no in Table 1 are not unique (highlighted in Orange) considering other columns except Amount. I was thinking of creating an intermediary Table but there I can’t create a unique composite key.
Finally, I incorporated the lookup Table into the detail one manually and things are working. However, this technique defeats the purpose of streamlining and automating the report if every month when I get GL data I have to manually bring the lookup Table as well. Any ideas/suggestions of how to design the model?

Thank you,
Helal

Table%201
Table 1.jpg697×782 139 KB
Table%202
Table 2.jpg1012×466 139 KB

#2

Can you post up some sample data? I’m being lazy and dont want to manually type all that :grinning:

Also, can you do a quick mock-up of what you would want the final table to look like?

#3

Nick,
Thank you for willing to help out. I prepared a file (enclosed zipped)

The file includes four tabs:

  • GL data: This is like fact table and contains detailed data

  • Insurance%_lkup: This is the lookup table with insurance names, codes, and percentages.

  • DesiredOutcomes: That’s the final Table

  • Excel Version: This is how it’s done in Excel version. Look at Final table where there is sumproduct formula. Most of the data come hard coded
    Ideally, I like to model (make one to many relationships) these in Tables, SQL, or Power BI so I can easily update month to month. Hope this make sense. I got it in Power BI by making Many to Many relationships and incorporated lookup table in to detailed data far from data modeling and streamlining the report. Anything else, let me know please.
    TestFile.zip (3.8 MB)

#4

@Helal-
Thanks for adding that file, really helpful. This is a good challenge! I will work on in it in the next few days and hopefully get something that can help you out

-Nick

#5

So got really close here, there are some small discrepancies from your desired output table and what I got, but the vast majority match. Maybe you could take a look since you are more familiar with this data?

Here’s what the matrix table looks like:

Matrix
Matrix.png796×286 23.7 KB

Here is the PBIX file:
M2M example.pbix (54.8 KB)
Everything was done in Power Query. I made a parameter where you enter in the location of the excel file:
File%20Location%20Parameter

and with that being said, in the test file:

  1. Create GLData in the Lookup table into Tables (click in the data and hit Ctrl+t)
  2. Name them GL and DimInsurance
  3. Save as TestFile

Then use that location for the parameter above.

-Nick

#6

Nick,
Awesome and thank you so much. However, I am having hard time following the steps in creating new Testfile. Should I combine the GLdata tab with Insurance%_lkup and then format it as Table and rename them to GL and Diminsurance? Will you also send me the new Test File?

#7

I think I made that part more complicated than it needed to be, sorry about that

TestFile.zip (3.6 MB)

That should work

#8

Great and thank you again. Let me see if I understood the steps. You basically grabbed the data from the Excel file I provided (GLdata and Insurance%_lkup) and formatted them as Table. Then you pulled them into Power BI and with Power Query you merged them into one final data set. If I am correct, then how do I incorporate/update the report with new GL data let’s say from Jan and Feb 2019? Also, how about making changes to Lkup Table (insurance) such as adding/deleting insurances and changing percentages. In sum how do I streamline, update, and maintain the report. I also do need to grab the amount by itself without multiplying it by %. Sorry for all the questions but I am fairly new to Power BI.

Thank You,
Helal

#9

That’s more or less what happened there. If you look at all the applied steps it got a little involved there, but that’s what made it fun :sunglasses:

Updating, well that’s the beauty of Power Query. All the steps I did will apply to whatever data is entered into those two tables in excel. Though they can come from wherever, changing were the source data comes from is not that hard.

Getting the amount by itself is definitely doable, as it exists somewhere just have to find it. I will try to get to that this weekend.

Also, one thing when you enter the file path in Power Query, be sure add in the \TestFile.xlsx

And dont apologize for asking questions, that is how we all learn. You most certainly picked a some-what complex case to get started in! But the more you use the more it will make sense

#10

Easier than I thought. Please see the attached file

Screenshot:

Matrix%20with%20Total%20Amt%20and%20Total%20of%20%25
Matrix with Total Amt and Total of %.png802×577 47.7 KB

File: M2M example.pbix (59.3 KB)

#11

Ah that’s fantastic. I very much appreciate your help and guided me thru the learning process. Just pick your brain and expertise, I was told that the best in data processing and visualization is best to do data modeling in SQL and get then connect the front end app such as Power BI, Tableau, etc…to the final and clean table in SQL. Any comments?

Thank you,

Helal

#12

Hi Nick,
One more question. I noticed that the GL table you created is an aggregate and account# is removed. The reports I am working on includes aggregate and detailed data. Another words, how do I retain account# from GLDATA?

Thank You,

Helal

#13

Another cool thing about Power Query is that if you get your data from a relational database (such as SQL) some of the transformations you do in PQ will actually be translated into SQL and those transformations will actually be done in SQL Server before coming to PQ, this is what we call Query Folding. Now, query folding is not available for all transformations and once the query folding is broken it is broken. So best to wait as long before you use a transformation that will break that query folding. That’s a whole different topic though. Basically do as much as you can at the source, but leverage query folding to make that as efficient as possible.

#14

There’s a way to get that data back in there. I’ll work on that tomorrow and post something up

#15

Thank you Nick.

#16

@Helal, please see the attached file. Here’s a screenshot with Group Names and Account on rows and Insurances on columns:

Matrix%20with%20Account%20%23
Matrix with Account #.png762×783 40.2 KB

Here’s the PBIX file as well:
M2M example.pbix (62.5 KB)

I’d highly recommend in going through step-by-step in applied steps in PQ to see what exactly is going on here to make this file work

-Nick

#17

Nick,
Thank you again and I would certainly follow the steps in the PQ. Just opened the latest M2M Power BI file and noticed that the Account #s are incorporated but lost Insurance Codes. Am I correct? I will look more closely though.

Thank You,

Helal