Forum Discussion
Helper IIIHow 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 Account | Year Returned | Value Returned |
| ABC | 2019 | $10 |
| ABC | 2020 | $20 |
| CBD | 2019 | $30 |
Thank you,
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
7 Replies
- amitchandak
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/
- parry2k
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.
- amitchandak
I agree to parry2k .
PBIUWOThe 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.
- parry2k
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.⚡
- v-luwang-msft
Community Support
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
