Forum Discussion
two rows per record dependent on date for value
I want to compare the tiers of 2019 vs 2020, by creating a column for each. Some companies may not have a 2019 or 2020 but all have at least one or both, meaning two records.
If a company has 2019 and 2020, it brings the record in twice. and displays the Tier on its own line -- I just need one row per company name. This is my result:
Desired result (THIS IS USING A MATRIX -- BUT DOESNT COVER WHAT I NEED, NEED COLUMN FOR EACH YEAR:
Do this on Power Query:
- select your column "Year"
- at "Transform" tab, hit "Pivot Column"
- select your column "Tier"
- on "Advanced options", choose "Don't aggregate"
4 Replies
I think you need to unpivot your columns? Hard to tell, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Greg_Deckler
bluedatenbrian -- TIER 2019 AND TIER 2020 are the columns im loooking to createLeft is my dataset--Right is desired. Will creating columns with DAX be enough to create those?
Do this on Power Query:
- select your column "Year"
- at "Transform" tab, hit "Pivot Column"
- select your column "Tier"
- on "Advanced options", choose "Don't aggregate"
Hi Cezar, You can do this:
- replace (blank) values with (null) at column 2020- order your set by 2019
- fill 2020 down (or up)
- filter yout set by 2019, removing (blank) values
Try this and I think you'll find your solution.
