Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Fun challange
Good job for including table format data in your post!!
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg
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! 🙂
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")
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |