Relation based on two columns

[Robin9700]

Hello!

I need help creating a relationship between two tables. I have two tables regarding ledger entries; one table with the details for each entry, the other table with the details for each entry-batch.

For example, in table 1 there are 3 columns: description, entry number and amount.

[Purchase of goods]   [entry 1]  [€100] 

[Purchase of service]  [entry 2]  [€350]

[Purchase of goods]   [entry 3]  [€20]

And in table 2 there are 4 columns: date, booking type, first entry number, and last entry number (of the batch)

[Date]  [Purchases]  [entry 1]  [entry 3]

Now, I want to show all purchases that are done on [Date]. So, I need table 2 to show the individual entries and their descriptions, instead of just the first and/or last one. Basically, I need a relationship based on 'first entry < entry# > last entry'. 

Does anyone know how to do this, with a DAX or PowerQuery for example?

Thanks!

[Robin9700]

@yingyinr Thanks again for your reply!

Table 2 is indeed exactly what I need.

Table 1 is like the one I already have.

I see I am not allowed to upload files and/or images, so I will try to explain this again.

Table 1 looks like this:

Description	Entry number	Amount
Purchase of goods	1	€100
Purchase of goods	2	€350
Purchase of goods	3	€20
Sales	4	€125
Sales	5	€250
Purchase of goods	6	€50
Purchase of goods	7	€100
Purchase of goods	8	€10

And the other table I have (which is not in your file) looks like this:

Date	Description	First entry number	Last entry number
May 12, 2021	Purchase of goods	1	3
May 17, 2021	Sales	4	5
May 20, 2021	Purchase of goods	6	8

And what I need right now, is a way to connect or merge those 2, to eventually create visuals with the following data (like table 2 in your file):

Date	Description	Entry number	Amount
May 12, 2021	Purchase of goods	2	€350
May 17, 2021	Sales	4	€125
May 20, 2021	Purchase of goods	6	€50
May 12, 2021	Purchase of goods	1	€20

So basically, what I need, is table 1 & 2 to form table 3 together. Can you help me with that?

[yingyinr · Accepted solution]

Hi @Robin9700 ,

I created another new sample pbix file(see attachment) base on the data of your provided tables, please check whether that is what you want.

Best Regards

[Robin9700]

Thank you so much! I was able to recreate the 'Entries' column and expand it, and now my data does what it should do 🙂 

Have a good day!

[yingyinr]

Hi @Robin9700 ,

Could you please provide some sample data(exclude sensitive data) in table 1 and table2 and your expected result with examples just as show in below tables? Thank you.

1. Sample data

table 1

description	entry number	amount
Purchase of goods	entry 1	100
Purchase of service	entry 2	350
Purchase of goods	entry 3	20

table 2

date	booking type	entry 1	entry 3
2021/5/12	purchase	20	30

2. Expected result?

date	booking type	entry number	description	value
2021/5/12	purchase	entry 1	Purchase of goods	20
2021/5/12	purchase	entry 3		30

Best Regards

[Robin9700]

Hi, thanks for your reply!

The result I want to show from the two tables, is:

Entry# - date - description - amount(€)

Table 1 shows the description and the amount. Table 2 contains the date and entrynumbers, however the entrynumbers are shown as "entry1 up to entry4", with both the first and last entry# of the 'batch' in different columns.

To see the date for 1 specific entry number, for example entry2, I need to use something like a VLookup would do in Excel, I think. However, I am not able to establish a relationship between the 2 tables at this moment. Do you have any suggestions?

[yingyinr]

Hi @Robin9700 ,

Sorry for delay. I created a sample pbix file(see attachment), please check whether that is what you want.

If the above one is not what you want, please provide some sample data in your tables and your expected result with specific examples and involved logic. Thank you.

Best Regards

[HotChilli]

If these columns are integers->  [entry 1]  [entry 3] , then a fairly elegant Power Query solution is available.

How do you link the batch between the 2 tables though?