0

I've got following 2 Tables:

 Date   Name  Value
1.1.19    A     11
1.1.19    B     54
1.1.19    C    234
2.1.19    A    346
2.1.19    B    654
3.1.19    A    156
3.1.19    B    6432
3.1.19    C    665
...

 Date   OtherName  Value
1.1.19      D       1
1.1.19      E       6
1.1.19      F       2
2.1.19      F       46
2.1.19      E       54
3.1.19      D       15
3.1.19      E       632
3.1.19      F       66
...

and i want to transform them into

 Date   Total Value  A.Value  B.Value  C.Value  D.Value  E.Value  F.Value
1.1.19     308         11       54       234       1        6        2
2.1.19     1100        346     654       0         0       54        46 
3.1.19     7966        156     6432      665       15      632       66

using Power Query in excel. I tried multiple approaches including Joins, Table.AddColumn with Table.SelectRow and whatnot. I cant seem to find any combination of formulas that fit my requirement.

Improve this question

1 Answer 1

Reset to default
0

Try this method:

  1. Add 2 Tables to Power Query Editor as Queries:

enter image description here

  1. Make 2 Tables' Title as the same and Append 2 Queries:

enter image description here

  1. Select Name column- go to Transform tab- Pivot Column- Value Column select Value and Advanced options select Sum- Close and Load:

enter image description here

Improve this answer
1
  • The OP also wants a Total Value column which you can get by adding a Number column with the Sum operation. But suggest you edit the M-Code so as to include all columns except the Date column, in case new Names appear in the future.
    – Ron Rosenfeld
    Commented Nov 2, 2019 at 0:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .