Forum Discussion
Create a new table pivot from calculated columns
Hi,
I am very new to this but trying to convert the following table where JAN - DEC are calculated columns:
| Resource | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| A | 0.5 | 1.5 | 0.1 | 0.1 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 2 |
| B | 0.1 | 0.1 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 2 | 1 | 1 |
| A | 0.5 | 0.3 | 0.1 | 0.1 | 0.1 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 |
| B | 0.5 | 1.5 | 0.1 | 0.1 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 0.3 | 2 |
| etc |
into the following:
| Resource | Month | Value |
| A | Jan | 1 |
| A | Feb | 1.8 |
| A | Mar | 0.2 |
| A | Apr | 0.2 |
| A | May | 0.4 |
| A | Jun | 0.6 |
| A | Jul | 0.6 |
| A | Aug | 0.6 |
| A | Sep | 0.6 |
| A | Oct | 0.6 |
| A | Nov | 0.6 |
| A | Dec | 2.3 |
| B | Jan | 0.6 |
| B | Feb | 1.6 |
| B | Mar | 0.4 |
| B | Apr | 0.4 |
| B | May | 0.6 |
| B | Jun | 0.6 |
| B | Jul | 0.6 |
| B | Aug | 0.6 |
| B | Sep | 0.6 |
| B | Oct | 2.3 |
| B | Nov | 1.3 |
| B | Dec | 3 |
Note I am ok if Resource is repeated for each instance of Month i.e. two values for Jan for Resource A
I cannot use a query as I have calculated columns and have tried DAX to pivot as follows... but get multiple repeats
NewTable=
UNION (
SELECTCOLUMNS (
OldTableName,
"Value", OldTableName[Jan],
"Month", "Jan",
"Resource", OldTableName[Resource]
),
SELECTCOLUMNS (
OldTableName,
"Value", OldTableName[Feb],
"Month", "Feb",
"Resource", OldTableName[Resource]
), etc...
Any help greatly appreciated!
Killian
Hi Summitreached,
If this is a case, you can try to add a distinct function outside of your formula, this function also works on table.
NewTable= DISTINCT( UNION ( SUMMARIZE( OldTableName, OldTableName[Resource], "Month", "Jan", "Value", OldTableName[Jan] ), SUMMARIZE( OldTableName, OldTableName[Resource], "Month", "Feb", "Value", OldTableName[Feb] ), etc... )Regards,
Xiaoxin Sheng
Hi Xiaoxin,
That worked perfectly!
Many thanks again,
Killian
6 Replies
Hi Summitreached,
You can try to use unpivot table function and remove duplicate function to convert these records.
Steps:
1. Click on edit queries button to enter query editor.
2. Select month columns and click on unpivot table.
3. All select table and click on remove duplicate button.Regards,
Xiaoxin Sheng
Hi Xiaoxin,
Thank you for the quick response. Unfortunately calculated columns do not appear in the Query editor.
Regards,
Killian
Hi Summitreached,
Can you share the sample data to analysis and write the formula?
Regards,
Xiaoxin Sheng