Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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
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
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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 .
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |