I have multiple dates and multiple deposits which i need to combine. please help

[sbarker_11]

Hi,

I have Applications from companies who have multiple approval and depoist lenders and dates. I need to report on these in a combined fashion so that I can show each company - number and value of deposits and approvals by date range. This will also help when reporting on indvidual Lenders. 

DATA EXAMPLE:

APP ID

Company Name

Approval 1 Date

Approval 1 Lender

Approval 1 Amount

Approval 2 Date

Approval 2 Lender

Approval 2 Amount

Approval 3 Date

Approval 3 Lender

Approval 3 Amount

Deposit 1 Date

Deposit 1 Lender

Deposit 1 Amount

Deposit 2 Date

Deposit 2 Lender

Deposit 2 Amount

Deposit 3 Date

Deposit 3 Lender

Deposit 3 Amount

4602

T

17/07/2024

MAXCAP

15,000

22/12/2023

FC TEST 1

20,000

15/02/2024

FC TEST 1

30,000

21/02/2024

Fundtest

5000

4566

U

12/12/2023

MAXCAP

40,000

07/02/2024

FC TEST 1

30,000

03/01/2024

FC TEST 1

30,000

25/01/2024

GOT 2

40,000

4528

V

02/02/2024

4TEST

80,000

20/12/2023

FC TEST 1

30,000

13/02/2023

Fundtest

5,000

13/02/2024

4TEST

65000

4478

W

11/01/2024

FC TEST 1

30,000

12/01/2022

GOT 2

2,600

15/02/2024

Fundtest

30,000

17/02/2024

GOT 2

2,600

REQUIRED OUTPUT:

TABLE 1 (APPROVALS)

APP ID	Company Name	Approval Date	Approval Lender	Approval No	Approval Amt
4602	T	17/07/2024	MAXCAP	Approval 1 Amount	15,000
4602	T	22/12/2023	FC TEST 1	Approval 2 Lender	20,000
4566	U	12/12/2023	MAXCAP	Approval 1 Amount	40,000
4566	U	07/02/2024	FC TEST 1	Approval 2 Lender	30,000
4528	V	02/02/2024	4TEST	Approval 1 Amount	80,000
4478	W	11/01/2024	FC TEST 1	Approval 1 Amount	30,000
4478	W	12/01/2022	GOT 2	Approval 2 Lender	2,600

TABLE 2 (DEPOSITS)

APP ID	Company Name	Deposit Date	Deposit Lender	Desposit No	Deposit Amt
4602	T	15/02/2024	FC TEST 1	Deposit 1	30,000
4602	T	21/02/2024	Fundtest	Deposit 2	5,000
4566	U	03/01/2024	FC TEST 1	Deposit 1	30,000
4566	U	25/01/2024	GOT 2	Deposit 2	40,000
4528	V	20/12/2023	FC TEST 1	Deposit 1	30,000
4528	V	13/02/2023	Fundtest	Deposit 2	5,000
4528	V	13/02/2024	4TEST	Deposit 3	65,000
4478	W	15/02/2024	Fundtest	Deposit 1	30,000
4478	W	17/02/2024	GOT 2	Deposit 2	2,600

[tharunkumarRTK]

@sbarker_11 

You can do this in Power Query.

Step 1: 

Create two reference queries, one for Approvals and another for Deposists.
Step 2:

Approvals Query:

1. Remove Deposit amount, Deposit Ledger and Deposit amount columns

2. Select Approval number and approval amount columns (both 1 and 2)

Step 3:

Deposits Query:

1. Remove all Approval related columns 

2. Unpivot deposit lender, deposit number and deposit amount coumns.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[sbarker_11]

Hi, I can unpivot but how do I deal with the multiple dates?

[tharunkumarRTK]

@sbarker_11 

As per your required output, you have one date column in approvals table and another one in the deposits table. You can create one date table in Power Query and add one relationship between approvals and new date table and deposits and date table. This way you will be able to add one date slicer to your report and can filter both the tables.

Hope it helps

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[sbarker_11]

Hi,

Thank you but it still doesnt work. I do require one date but the date list should represent a list of deposit date 1, deposit date 2, deposit date 3 etc which will have different lenders and deposit amounts... when i unpivot the columns, the data then gets mixed up even if i unpivot the dates first.

[tharunkumarRTK]

@sbarker_11 
Thanks for the clarification. For your requirement, I would not suggest you to unpivot the columns. Instead, create one active relationship (approvals<-dateTable and datetable -> deposits) and create inactive relationships for all other date columns. Active the inactive relationships whenever required in your measures.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun