Forum Discussion

Summitreached's avatar
Summitreached
Frequent Visitor
9 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    Summitreached
    9 years ago

    Hi Xiaoxin,

     

    That worked perfectly!

     

    Many thanks again,

    Killian 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Summitreached
Frequent Visitor
9 years ago

Hi Xiaoxin,

 

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

 

Regards,

Killian

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Summitreached,

     

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

     

    Regards,

    Xiaoxin Sheng

    • Summitreached's avatar
      Summitreached
      Frequent Visitor
      9 years ago

      Hi Xiaoxin,

       

      All the information is given above.

       

      Thanks again,

      Killian

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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