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?