Merging - Returning multiple rows

[lync0056]

Hello helpers,

I need some assistance with a merge challenge. I'm not sure if it should be solved in the Query editor or as a Calculated Column.

I need to merge & match potentially multiple Credit Notes into the Invoices table

Credit Notes Table has –

Invoice ID Column

Invoices Table has –

Invoice ID Column

The Credit Note Table has many Credit Notes, and each Credit Note belongs to an Invoice ID. 

So in the Invoice Table, for each row, I need to show all the Credit Note IDs associated with that Invoice ID.

Any help would be great.

Kind regards

Cameron

[Thejeswar]

---

@lync0056 wrote:

Hello helpers,

I need some assistance with a merge challenge. I'm not sure if it should be solved in the Query editor or as a Calculated Column.

I need to merge & match potentially multiple Credit Notes into the Invoices table

Credit Notes Table has –

Invoice ID Column

Invoices Table has –

Invoice ID Column

The Credit Note Table has many Credit Notes, and each Credit Note belongs to an Invoice ID. 

So in the Invoice Table, for each row, I need to show all the Credit Note IDs associated with that Invoice ID.

Any help would be great.

Kind regards

Cameron

---

From your earlier post, I understand that you have two tables 

1. Credit Notes Table has –

Invoice ID Column

Credit Note Column (Ideally this should be available)

2. Invoices Table has –

Invoice ID Column

Out of these 2 tables, one of the table should also have the Credit Note information. In the sense, a table will have Invoice ID Column while the other table will Invoice ID and Credit Note column in the same table.

So the Invoice ID Table will have Unique invoice IDs while the credit not table will have Unique Credit Note will same Invoice ID for Many Credit Notes.

In this scenario, merging the tables using Invoice ID in a One to Many relationship, will merge the tables as per your need.

If my understanding is not right, then give me some more info on the table structure and usage so that I can help further

[lync0056]

There is no relationship between these 2 tables. (Invoices & Credit Notes)

Neither table the Invoice ID is unique.

Invoices Table - Invoice Line ID is unique

Credit Notes - Credit Note Line ID is unique

Many Invoice Line IDs belong to 1 Invoice ID

Many Credit Note Line IDs belong to 1 Credit Note ID

[Thejeswar]

Hi @lync0056,

You can merge the two queries based on Invoice ID Column in the Power Query Editor Window

In the Top Ribbon of the Query Editor Window, you will find the Merge Option as shown in the screenshot below

Append Option

Click on Merge, you get this

Hope this helps!!

If this solves your need, accept the solution and give it a kudos!!!

[lync0056]

Their are multiple Credit Notes that can be connected to the 1 Invoice ID. So I need the Credit Notes associated with that Invoice ID to be returned as an array or in separate columns. 

This does not happen using Merge by itself.