cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cesarvaldez
Helper II
Helper II

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:

Capture1.JPG

Desired result (THIS IS USING A MATRIX -- BUT DOESNT COVER WHAT I NEED, NEED COLUMN FOR EACH YEAR:Capture.JPG

1 ACCEPTED SOLUTION

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"

 

View solution in original post

4 REPLIES 4
bluedatenbrian
Advocate II
Advocate II

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. 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler 
@bluedatenbrian -- TIER 2019 AND TIER 2020 are the columns im loooking to create

Left is my dataset--Right is desired. Will creating columns with DAX be enough to create those?

Capture.JPG

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"

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.