Pivoting Columns into one row

[NickDSL]

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

[AlienSx · Accepted solution]

= 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)