cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Regular Visitor

Re: two rows per record dependent on date for value

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
Highlighted
Super User IV
Super User IV

Re: two rows per record dependent on date for value

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Regular Visitor

Re: two rows per record dependent on date for value

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. 

Highlighted
Helper I
Helper I

Re: two rows per record dependent on date for value

@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

Highlighted
Regular Visitor

Re: two rows per record dependent on date for value

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors