Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Robert Jolliffe]

I solved this issue but I have no idea how.

I have 2 tables

Table 1 has a field that is either Invoice or Credit, and a field with a Document No.  I concatenated the 2 fields to create a new one called Doc No. Type
EG:
Amount   Type                 Doc No.   Doc No. Type
$100     Invoice                 101     Invoice-101
$200     Invoice                 202     Invoice-202

The second table has similar fields

Date           Other Data    Type                 Doc No.   Doc No. Type
01-01-2021     A              Invoice                 101     Invoice-101
01-15-2021     B              Invoice                 202     Invoice-202
01-17-2021     C              Invoice                 202     Invoice-202
01-21-2021     D              Payment                 303     Payment-303

When I create a 1:n join between Table 1 and Table 2 – and add a field from Table 1 to a “Table” visualization, all is fine.  When I added a field from Table 2 – all records cleared and no data was visible.

As I played around sometimes the payment was visible, sometimes nothing etc…

So I started a new tab and dragged in the fields from Table 2 and started adding Table 1 just to see what would happen.  That worked fine.  I can rearrange the fields to the exact order I wanted to.

Does anyone have any idea why this happened?  It is essentially an outer join as Table 2 has records in it that aren’t n Table 1.  I am a little confused by the join types in Power BI models  – I’m an old hand at SQL but I do find the limits of not being able to use compound keys very limiting and the cross filters etc…  Could that be what the issue is?  Is there a primer on this stuff for someone who knows SQL?

Maybe I should do this in the the power query as merging queries and expanding Table 2 … maybe do this with transformations not in the model relationships?  I just really don’t want to have to do magic to get the SUM of the invoice 202 as I would probably end up with a new query where it shows 2x where the 2 are merged into 1.

Thanks in advance!

-Rob

——————————
Robert Jolliffe
President
Sabre Limited
Cambridge, Ontario Canada
5195857524
——————————

[William Skelley]

Hi Rob:

There are all sorts of table functions for you to use. Summarize, Summarize Columns, CROSSJOIN, Intersect, etc. 

If you have an example of some tables and the result you are seeking I can try to help. It’s great to have the data to work with.

Thanks,

Bill

——————————
William Skelley
01106
Longmeadow MA
8602807221
——————————
——————————————-