Forum Discussion

Advocate I

9 years ago

Solved

create table from existing table

Hi all.

I'm getting used to DAX expressionswith columns, but not with tables.

I want to create new table for waterfall chart.

Here is the part of big table I have (1) and my target table (2).

-->

Could you guys help me find the best way to do this?

Appreciate.

Tips and Tricks

hi. is possible that exists better solutions but i present one

TableNew2 =
UNION (
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR A]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "B",
        "WEIGHT", Table1[FACTOR B]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR C]
    )
)

Or

TableNew =
UNION (
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR A], "FACTOR", "A" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR B], "FACTOR", "B" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR C], "FACTOR", "C" )
)

4 Replies

Community Champion

It can easily be done via the Query Editor, using Unpivot as illustrated in this video.

karimkz
Advocate I
9 years ago
Thanks, you're awesome.

Advocate I

Actually, my columns are calculated columns, so Edit Query doesn't work here.

Any ideas?

Community Champion

hi. is possible that exists better solutions but i present one

TableNew2 =
UNION (
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR A]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "B",
        "WEIGHT", Table1[FACTOR B]
    ),
    SELECTCOLUMNS (
        Table1,
        "UNIT", Table1[UNIT],
        "DATE", Table1[DATE],
        "FACTOR", "A",
        "WEIGHT", Table1[FACTOR C]
    )
)

Or

TableNew =
UNION (
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR A], "FACTOR", "A" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR B], "FACTOR", "B" ),
    SUMMARIZECOLUMNS ( Table1[UNIT], Table1[DATE], Table1[FACTOR C], "FACTOR", "C" )
)