Forum Discussion
Merge the tables
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 5 columns:
- "Material Code"
- "Extraction date"
- "ADI number"
- "Cause ADI number"
- "Stock quantity"
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) | Cause ADI number (A column with duplicate rows) | Stock quantity |
| 01/01/2020 (always the date of the first of the month) | 36126201 | 15/12/25 | no more need | 25 |
| 01/02/2020 | 3612622535 | 11/12/27 | human problem | 30 |
| 01/03/2020 | 3612622345344 | 14/12/29 | shopping problem | 1500 |
| 01/01/2020 | 36126201 | 18/12/86 | human problem | 36 |
| 01/04/2020 | 3625 | 19/30/56 | human problem | 59 |
in my Master table I have 4 columns:
- "Extaction Date"
- "Material code"
- "Forcast"
- "ABC" Code
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) | forecast | ABC |
| 01/01/2020 (always the date of the first of the month) | 36126201 | 200 | A(In January we have the code which was categorized in ) |
| 01/02/2020 | 3612622535 | 300 | B |
| 01/03/2020 | 3612622345344 | 400 | C |
| 01/03/2020 | 36126201 | 740 | B (In March we have the code which was categorized now in) |
I would like to have in my table "master TABLE" for each article code my ADI number and the cause ADI number which is in the table "DAD_Factory", I also want that we take into account the date of extraction of the two tables to try to have a historical follow-up.
I tried the solution to merge my two tables, but this is not possible because it doubles my rows in the "Master_table" and suddenly my forcaste too.
Can a person help me find a solution!
Can someone help me?
Hello Anonymous
didn't you post this or at least a very similar question already? What changed now?
BR
Jimmy
Hello
what about creating a material table (extracting all material code from your both table and get distinct value) and a date table, connect all three tables and then use a matrix like this
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
JimmyHello Anonymous
for the date-table is better to create i manually, and not extracting others dates. So just use function like List.Dates with 3 parameters and create a list of dates and then transform it to a table. What you can however do ist to calculate the start date form your earliest date present in the tables. Hope I was clear enough
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
9 Replies
Hello Anonymous
didn't you post this or at least a very similar question already? What changed now?
BR
Jimmy
Hello Jimmy801 ,
Your solution was great, but I realized that when I group my ADI codes, I lose the possibility of transmitting the other columns of my DAD FACTORY table.
Maybe I misspoke the first time.
but I will try to do it again now :
What I really want to do is that when I filter on a date of the extraction, I have the possibility to see for an item code the different ADI numbers that it had and the different causes of ADIs at the period that j've filtered.
Example:
when I filter for 01/01/2021, I know that at that time I had the article code 567 and that it had the ADI number "13/25/456" and that the cause of this ADI is the "Human Cause".
hope you understood my request.
Thanks jimmy
Hello Anonymous
you could then add a key-column to both of your tables where your are combining date and material number. In Power BI create a realationship between them and visual your data. Will this work out for you?
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
JimmyI just tried your method and it doubles the lines.
don't you know another solution?regards,