Merge the tables

[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 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?

[Jimmy801 · Accepted solution]

Hello @Anonymous 

didn't you post this or at least a very similar question already? What changed now?

BR

Jimmy

[Jimmy801 · Accepted solution]

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

Jimmy

[Anonymous]

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

[Jimmy801]

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

Jimmy

[Anonymous]

@Jimmy801 

I just tried your method and it doubles the lines.
don't you know another solution?

regards,

[Jimmy801 · Accepted solution]

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

Jimmy

[Anonymous]

I believe this works.

Do you know how I have to extract the item codes in my Master Table?

[Jimmy801]

Hello @Anonymous 

you mean material codes?

I used this code to get the material-table. I combined both columns of material code, get distinct values and then transformed into a table

let
    Source = List.Distinct(List.Combine({DAD[Material Code], Master[Material Code]})),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Material Code"}})
in
    #"Renamed Columns"

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

[Anonymous]

it works, thanks for your help.

One last question for the end (I promise it's the last).

Do you know how I should do to extract the "Date of extraction" column and the "MaterialCode" column from my "Master_Table" table.

[Jimmy801 · Accepted solution]

Hello @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

[Jimmy801 · Accepted solution]

Hello @Anonymous 

didn't you post this or at least a very similar question already? What changed now?

BR

Jimmy