Linking Joint Client Income

[DataMinion]

Hi,

I am new to this world and trying to create a joint client income report using Power Query. I have basic knowledge on Power Query and Power Pivot but would consider myself knowledgeable in Excel so linking reports like this I would typically use the SUMIFS formula.

I have tried using Merged Queries but it seems to take an absolute age to run the report as these reports can have in excess of 80k rows.

I feel as though the better option would be to use a Measure and this is where I need help from someone.

I have 2 tables, one table is a joint client list which shows clients that are either sole client ( client 1) or where they are joint (client 2), they can be linked for example where they are married. The other table shows the income we have received for the year from each client and this can be from either client 1 or client 2.

I am trying to run a report to show the total income earned for the year for both clients, where they are joint then this income is added together. 

I'm sure this is a typical scenario for a many to many relationship but I can't quite figure it out. I have tried using the CrossFilter function but I'm really guessing on how to do this correctly. I have a bridge table which can be used as there are further details on the source of the income that I can obtain, e.g. account number.

To try and show you a brief description, the tables are as follows:

Joint client list

Client 1 Name	Client 1 ID	Client 2 Name	Client 2 ID	Client(s)	Client 1 Income	Client 2 Income	Total Income
A Brown	101			A Brown	£60		£60
A Smith	102	B Smith	103	A & B Smith	£60	£20	£80

Income Report

Provider	Client ID	Income
Investec	101	£50
Standard Life	101	£10
Investec	102	£60
Bank of America	103	£20

Thank you for your time on this and I'm sure it has come through loud and clear that I am a newbie on this but would appreciate anyone's help.

Kind regards,

Steve

[rohit_singh · Accepted solution]

Hello @DataMinion ,

Please try this in Power Query ;

1) Load the tables 

Table 1: PQ_Joint client list

Table 2: PQ_Income Report

2) Perform a group by operation on table 2, grouping on client id and summing up the income

PQ_Income Report query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrSy0uSU1W0lEyNDAEkocWmxooxepEKwWXJOalJBalKPhkpqUiSRtCpFE0GoFlzCAyTol52Qr5aQqOualFmcmJYAXGYAVGQAWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Provider = _t, #"Client ID" = _t, Income = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Provider", type text}, {"Client ID", Int64.Type}, {"Income", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client ID"}, {{"Income", each List.Sum([Income]), type nullable number}})
in
    #"Grouped Rows"

3) On table 1, perform a merge operation to link table 1 with table 2. Join Client 1 ID from table 1 to Client ID on table 2

Expand rows on the new column to get Client 1 total income

4) Repeat step 3, joining Client 2 ID from table 1 to Client ID on table 2 , and expand rows as above. This wil give you Client 2 Total income.

5) Replace any null values in the income columns with 0, and add a new column called "Total Income" that is the sum of client 1 total income and client 2 total income.

PQ_Joint client list query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRwKsovz1PSUTI0MASSEAQTjdUBqQjOzSzJAKswApJOSHxjsNqYUgMDIzMFmERsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client 1 Name" = _t, #"Client 1 ID" = _t, #"Client 2 Name" = _t, #"Client 2 ID" = _t, #"Client(s)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client 1 Name", type text}, {"Client 1 ID", Int64.Type}, {"Client 2 Name", type text}, {"Client 2 ID", Int64.Type}, {"Client(s)", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Client 1 ID"}, #"PQ_Income Report", {"Client ID"}, "PQ_Income Report", JoinKind.LeftOuter),
    #"Expanded PQ_Income Report" = Table.ExpandTableColumn(#"Merged Queries", "PQ_Income Report", {"Income"}, {"Income"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded PQ_Income Report", {"Client 2 ID"}, #"PQ_Income Report", {"Client ID"}, "PQ_Income Report", JoinKind.LeftOuter),
    #"Expanded PQ_Income Report1" = Table.ExpandTableColumn(#"Merged Queries1", "PQ_Income Report", {"Income"}, {"Income.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded PQ_Income Report1",{{"Income", "Client 1 Income"}, {"Income.1", "Client 2 Income"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Client 2 Income"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Total Income", each [Client 1 Income] + [Client 2 Income])
in
    #"Added Custom"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[DataMinion]

Thanks @rohit_singh that did the trick after removing duplicates as it would not let me Merge until I did that first.

I appreciate your help.

Cheers

Steve

[rohit_singh]

You're welcome @DataMinion ! Please do click on the thumbs up button to leave me kudos! Will really appreciate it!

Kind regards,

Rohit

[DataMinion]

@rohit_singh consider it done, cheers.

[rohit_singh]

Hello @DataMinion ,

Not sure if this works for you but please try this : 

1) Load the tables 

Table : Joint client list

Table : Income Report

2) Create two identical tables from Income Report, summarizing client ID and income. Name these Client 1 Summary and Client 2 Summary

3) Create relationships between the tables. Join 'Joint client list'[Client 1 ID] to 'Client 1 Summary'[Client ID] and then 'Joint client list'[Client 2 ID] to 'Client 2 Summary'[Client ID]

4) Create two calculated columns on table Joint client list to fetch data from tables client 1 summary and client 2 summary.

Client 1 Income = related('Client 1 Summary'[Income])

Client 2 Income = related('Client 2 Summary'[Income])

5) Finally, add a new column that is the sum of Client 1 income and Client 2 Income

Total Income = 'Joint client list'[Client 1 Income] + 'Joint client list'[Client 2 Income]

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  😊

[DataMinion]

Hi @rohit_singh 

Thank you for your help on this.

Some of those screenshots look like Power BI and currently I'm looking to find a solution to this within Power Query. My knowledge in both formats is not great so I'm just trying to work with Power Query for now and then move on from there.

I followed your steps and when I tried to link Client 2 Summary to the 2nd client on the Joint Client Report I had an error saying there were duplicates. There are no duplicate Client IDs so I can only think this is due to there being blank cells as not all clients are joint.

How can I link the table when there are blanks, is there a way to workaround this?

Cheers