Forum Discussion
Solved
Pivoting Columns into one row
I have data like below.
| Products | Date | Value |
| 1 | 1/1/2023 | 10 |
| 2 | 1/1/2023 | 10 |
| 1 | 1/2/2023 | 20 |
| 2 | 1/2/2023 | 20 |
| 1 | 1/3/2023 | 30 |
| 2 | 1/3/2023 | 30 |
| 1 | 1/4/2023 | 40 |
| 2 | 1/4/2023 | 40 |
| 1 | 1/5/2023 | 50 |
| 2 | 1/5/2023 | 50 |
I'm trying to pivot into into this table below.
| Date | 1 | 2 |
| 1/1/2023 | 10 | 10 |
| 1/2/2023 | 20 | 20 |
| 1/3/2023 | 30 | 30 |
| 1/4/2023 | 40 | 40 |
| 1/5/2023 | 50 | 50 |
When I do Pivot columns I get this below:
| Date | 1 | 2 |
| 1/1/2023 | 10 | null |
| 1/1/2023 | null | 10 |
| 1/2/2023 | 20 | null |
| 1/2/2023 | null | 20 |
| 1/3/2023 | 30 | null |
| 1/3/2023 | null | 30 |
| 1/4/2023 | 40 | null |
| 1/4/2023 | null | 40 |
| 1/5/2023 | 50 | null |
| 1/5/2023 | null | 50 |
There are other columns and this is a heavily simplified version as I cannot share the exact data.
If anyone has any tips or trick on how I can get to my goal (imaged below here again) I would greatly appreciate it!
| Date | 1 | 2 |
| 1/1/2023 | 10 | 10 |
| 1/2/2023 | 20 | 20 |
| 1/3/2023 | 30 | 30 |
| 1/4/2023 | 40 | 40 |
| 1/5/2023 | 50 | 50 |
= Table.Pivot(Table.TransformColumnTypes(Source, {{"Products", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source, {{"Products", type text}}, "en-US")[Products]), "Products", "Value", List.Sum)
1 Reply
= Table.Pivot(Table.TransformColumnTypes(Source, {{"Products", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Source, {{"Products", type text}}, "en-US")[Products]), "Products", "Value", List.Sum)