Column merge Help !!!!

[Anonymous]

Good morning all !

I would like your help on a problem that I have been encountering for weeks!

I will explain my situation to you very quickly:

In my Power Bi report, I have two tables:

1. A "DAD Factory" table
2. A"Master table" table

In my "DAD factory" table I have 3 columns:

• "Material Code"
• "Extraction date" 
• "ADI number" 

It should be noted that there are several duplicates in this table.
For example a material code can have several ADI Number and several extraction date:

Here's a quick example (in red you can see the duplicates😞

Extraction date (A column with duplicate rows)	Material Code (A column with duplicate rows)	ADI number (A column with unique data)
01/01/2020 (always the date of the first of the month)	36126201	15/12/25
01/02/2020	3612622535	11/12/27
01/03/2020	3612622345344	14/12/29
01/01/2020	36126201	18/12/86
01/04/2020	3625	19/30/56

in my Master table I have three columns:

• "Extaction Date"
• "Material code" 
• "Quantity"

I also wish to specify that in this table I also have several duplicates (in red you can see the duplicates).

here is a quick example:

Extraction date (A column with duplicate rows)	Material Code (A column with duplicate rows)	Quantity
01/01/2020 (always the date of the first of the month)	36126201	200
01/02/2020	3612622535	300
01/03/2020	3612622345344	400
01/01/2020	36126201	740

What I'm trying to do is to be able to extract the set of ADI numbers from the "DAD Factory" table which are linked to a material code and to link it to the matarial code of the Master table (like a VLOOKUP in Excel).

So I tried to merge the two tables, but as there are duplicates in each table, this recreate new duplicates in my master table

Can someone help me?

[Anonymous]

@Jimmy801 

you are a monster, you are too strong your solution works !! 🤗

I just have a quick question, do you know a way to remove duplicates that may appear in the "ALLADI" column

thank you for your help !

[Jimmy801 · Accepted solution]

Hello @Anonymous 

then I would appreciate if you could mark my post as solution. 

About your other question... what you mean duplicates... you a line with date, material and ADI where in the ADI-column you have two time a ADI-number? Then use this code (not tested)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1LCsAgDIThu2QtJJkkVs8i3v8aPgqWlsIsZvHB3xqJ8hwEQoksKzJE59VgBSOopxvhjRAWi+lm12H2YeZh7kv6lvXI32pZqOSD/EHYvcomHBP0AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extraction date" = _t, #"Material Code" = _t, #"ADI number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extraction date", type date, "en-US"}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Extraction date", "Material Code"}, {{"AllADI", each Text.Combine(List.Distinct(_[ADI number]), ", "), type text}})
in
    #"Grouped Rows"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801]

Hello @Anonymous 

you can manipulate your DAD-table in a way that you don't have any duplicates anymore. Group material and date and use Text.Combine to concatenate your ADI-numbers. After this is done you can join your DAD-table to your master table by date and material number and no row will be duplicated. Hope I was clear 🙂

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1LCsAgDIThu2QtJJkkVs8i3v8aPgqWlsIsZvHB3xqJ8hwEQoksKzJE59VgBSOopxvhjRAWi+lm12H2YeZh7kv6lvXI32pZqOSD/EHYvcomHBP0AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extraction date" = _t, #"Material Code" = _t, #"ADI number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extraction date", type date, "en-US"}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Extraction date", "Material Code"}, {{"AllADI", each Text.Combine(_[ADI number], ", "), type text}})
in
    #"Grouped Rows"

Outcome

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy