Forum Discussion

Helper III

5 years ago

Solved

How can I create 1 column by appending multiple column values, and also add a date field?

Hi, I have a SUMMARIZE Table (with column names) that looks like this. *summarize because the data has multiple tables, and so i had to use a DAX formula for it. Customer Account | Value ...

Need Help

v-luwang-msft

5 years ago

Hi PBIUWO ，

You could try like following:

base data:

then create a new table by the following measure:

Table 2 =
UNION (
    SELECTCOLUMNS (
        'Table',
        "Customer Account", 'Table'[Customer Account],
        "Year Returned", "2019",
        "Value Returned", 'Table'[Value Returned 2019]
    ),
    SELECTCOLUMNS (
        'Table',
        "Customer Account", 'Table'[Customer Account],
        "Year Returned", "2020",
        "Value Returned", 'Table'[Value Returned 2020]
    ),
    SELECTCOLUMNS (
        'Table',
        "Customer Account", 'Table'[Customer Account],
        "Year Returned", "2021",
        "Value Returned", 'Table'[Value Returned 2021]
    )
)

And final you will get :

Wish it is helpful for you!

Best Regards

Lucien

create 1 column by appending multiple column values.pbix28 KB

parry2k

Super User

5 years ago

PBIUWO although the calculated column for 2021 is a simple calculation that you can easily do in PQ. Having said that, do the calculation in PQ, append the tables, and then use unpivot as recommended by amitchandak

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡