cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBIUWO
Helper III
Helper III

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 Returned 2019 | Value Returned 2020 | Value Returned 2021 


From this, I want to add another column called "Year Returned", that is a date value. 

And then, add another column "Value Returned" which is the values from the 3 columns (Value Returned 2019, Value Returned 2020, Value Returned 2021) 

 

The table then would look like this, 

Customer AccountYear ReturnedValue Returned
ABC2019$10
ABC2020$20
CBD2019$30

 

 

 

Thank you, 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @PBIUWO ,

You could try like following:

base data:

v-luwang-msft_0-1622622662634.png

 

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 :

v-luwang-msft_1-1622622765291.png

 

 

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

View solution in original post

7 REPLIES 7
v-luwang-msft
Community Support
Community Support

Hi @PBIUWO ,

You could try like following:

base data:

v-luwang-msft_0-1622622662634.png

 

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 :

v-luwang-msft_1-1622622765291.png

 

 

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

View solution in original post

parry2k
Super User
Super User

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

Okay, I'll that.

 

Is there a function that does the same as "LOOKUPVALUE" in Power Query? 
I would need to bring the item cost column into the table.

parry2k
Super User
Super User

@amitchandak Unpivot will not work if tables are summarized using dax. 

 

@PBIUWO How your raw data look like? What is your DAX expression for summarize? As @amitchandak  suggested, it is easier to do it in PQ but for that, you need to share how your raw data looks like.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

The Summarize table has 2 tables appended by UNION as well,

 

1. Customer Account |  Value Returned 2019 | Value Returned 2020 

*Now static file, nothing is changed

2. Customer Account | Date Requested | Item Code | Value Returned 2021 

*Date Requested date can be older than 2021, but is returned in 2021, so it is captured as 2021 Value Returned

*Value Returned 2021 is a calculated column, multiplying quantity and item cost

 

So the Summarize created a new calculated column, Value Returned 2021. 

Customer Account |  Value Returned 2019 | Value Returned 2020 | Value Returned 2021 

 

The question was raised because the problem was that the date requested is 1 date value, but the items can be returned in multiple years. 
Ex. Requested in 2019, but some $ returned in 2019, and 2020. So for each record, you can have 2 date returned. 

 

I can't do it in PQ because that 2021 returned value is a calculated column. 

I agree to @parry2k .

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
amitchandak
Super User
Super User

@PBIUWO , Based on what I got First Unpivot the Value Returned 2019 | Value Returned 2020 | Value Returned 2021 

https://radacad.com/pivot-and-unpivot-with-power-bi

Post that split the first column having the year on last occurrence of space

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!