Merge Tables Without Duplicating Data

Power Query
1

Hi Everyone

I am trying to merge an AR table with a table of comments however I am currently duplicating the AR data when merging the tables therefore giving spurious results.

I have attached a sample of the data in an Excel sheet (what would be the source data) and also the PBIX file of how I am processing it. The Excel sheet has the two data tables on the AR_Data and AR_Comments tabs. The current keys between both tables are the invoice numbers and project codes.

The Intended_Results table is what I would like to achieve in PowerBI without duplicating the lines within the AR_Data tab but it doesn’t seem to work with the way I am merging the tables in PowerQuery.

As you can see within the PBIX file, the Balance gets duplicated for each invoice due to the several different comments which are merged from the AR_Comments table.

Therefore, any help on this would be greatly appreciated.

Thanks,
Nikesh
PBI Query Merge.pbix (66.4 KB)
AR Sample Data.xlsx (14.4 KB)

2

Hi @nikeshvalji,

Welcome back…
See if this meets your requirement.

image
image1106Ă—298 19.7 KB

Here’s what I’ve done, merged on “Invoice” and “Project Name”
Extracted lists with “Comment Date” and “Comment” from that table and combined those strings.

Here’s your sample file.
eDNA - Merge wo duplication.pbix (34.4 KB)

I hope this is helpful

1 Like
3

Hey @Melissa

Thanks so much for this! I will try it out and let you know how I get on :+1:

Nikesh

1 Like
4

Hello @nikeshvalji, a gentle follow up if the solution provided by @Melissa worked for you.

5

Hello - yes it worked absolutely fine.

Thanks again @Melissa for the quick and expert advice!

1 Like