Forum Discussion
Data modeling wanted to create new table from existing table
wanted to create new table from existing table
existing table
| Country | Jan | feb | mar |
| Albania | 1 | 1 | 2 |
| Algeria | 2 | 2 | 1 |
| Angola | 1 | 1 | 2 |
| Angola | 2 | 2 | 1 |
| Argentina | 1 | 1 |
wanted new table like below
| Month | total |
| Jan | 6 |
| feb | 7 |
| mar | 7 |
3 Replies
- christinepayton
Most Valuable Professional
Use "unpivot other columns" with country selected in Power Query (there is an option in settings for how you want to aggregate when unpivoting, this looks like "sum"). If you want a second table, you can duplicate or reference the original table before doing this.
- christinepayton
Yeah, so like this:
Duplicate or reference the original table first, if you want a second table (it's in the right-click menu when you select the table in Power Query). That gives you this:
I misspoke about the aggregate during the unpivot, that is only for going in the other direction and pivoting it turns out. But you can still aggregate in other ways such as group and sum in Power Query, or just write a measure in DAX for "Month Total = SUM(Table[Value])" and drop it in a chart. You are going to run into issues with sorting at that point because you're trying to use month names as a date and it's text, so you could add a conditional column and assign the months a number 1-12, then sort by that column (https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-desktop).
DAX is the better way to go here because it'll let you use the country as a slicer or dimension.
