Create a new table pivot from calculated columns

[Summitreached]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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

[Summitreached]

Hi Xiaoxin,

Thank you for the quick response. Unfortunately calculated columns do not appear in the Query editor.

Regards,

Killian

[Anonymous]

Hi @Summitreached,

Can you share the sample data to analysis and write the formula?

Regards,

Xiaoxin Sheng

[Summitreached]

Hi Xiaoxin,

All the information is given above.

Thanks again,

Killian

[Anonymous · Accepted solution]

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

[Summitreached · Accepted solution]

Hi Xiaoxin,

That worked perfectly!

Many thanks again,

Killian