Replace Values from Different tables

[Anonymous]

Hey Guys,

I need help with this query in M-code.

I have one table which has general data and is quite big and slow (Table1). I also have another table which has newer data, fast and shiny (Table2). I need a new table "Table3" which has all values and colums of Table1, but everytime Table2 and Table1 have the same column name, I need the "newer" value from Table 2.
Also, I need all columns from both Table1 and Table2

It should look like this:

Is there a fast way to do this

Thanks for your help, really appreciate it. 3 tables below.

Greetings,

Janik





Table1

IdentNo	Value1	Value2	Value3	Value21	Value30
1	196	89	2045	1515	1006
2	591	82	7541	9571	6629
3	784	2	1006	12	9571
4	503	84	6629	6629	84
5	739	40	9571	9571	40
6	867	58	557	23	523
7	523	84	6334	125125	768
8	768	92	3860	23	956
9	956	5	7854	2323	5

 Table 2

IdentNo	Value1	Value3	Value21	Value_New1
2	739	123	35	123
4	2	1006	84	43433
5	84	232	1512	152
6	40	9571	2312	52422

Table3(Goal)

IdentNo	Value1	Value2	Value3	Value21	Value30	Value_New1
1	196	89	2045	1515	1006
2	739	82	123	35	6629	123
3	784	2	1006	12	9571
4	2	84	1006	84	84	43433
5	84	40	232	1512	40	152
6	40	58	9571	2312	523	52422
7	523	84	6334	125125	768
8	768	92	3860	23	956
9	956	5	7854	2323	5

[NickolajJessen]

Fun challange

Good job for including table format data in your post!! 

https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg

[Anonymous]

Thanks for your answers.

I need a solution directly in M with a query in the end as I do this in Power BI Dataflows and need a data table like Table3.

Any suggestions in M?

Thanks! 🙂

[NickolajJessen]

Just pivot table2 it back to original format 🙂

Add this to the last step on table 2:

= Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value")