Forum Discussion
Column merge Help !!!!
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:
- A "DAD Factory" table
- 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?
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
3 Replies
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
Jimmyyou 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 !
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