cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TBenders Regular Visitor
Regular Visitor

Combining rows based on unique id, and combining information

Hi,

 

This is my first post on this forum, but I've been 'lurking' around for a while, learning as I go. Hopefully one of you BI-wizards can help me out with my particular case for which I couldn't find an anwser.

 

In my Power BI I have a table with id's and dates. I'm trying to combine the rows based on the id (so this column will be a unique identifier) while combining the data from the differen't columns.

Example of my table:

id

            StartDate

            FollowupDate

            FinishedDate

101

            1-1-2016

            null

            null

101

            null

            1-2-2016

            null

101

            null

            null

            2-2-2016

102

            3-1-2016

            null

            null

102

            null

            null

            5-1-2016

103

            3-1-2016

            null

            null

 

Result I'm trying to get:

id

StartDate

            FollowupDate

            FinishedDate

101     

1-1-2016

            1-2-2016

            2-2-2016

102

3-1-2016

            null

            5-1-2016

103

3-1-2016

            null

            null

 

I have a feeling this should be easy to do, but I've been searching around for quite a while without result.

 

Any idea how I can accomplish this in Power BI?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Combining rows based on unique id, and combining information

Sorry, forgot to mention that you need to turn the aggregation off: last step: Advanced Opions: Don't aggregate bofore hitting the last OK.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




14 REPLIES 14
Super User
Super User

Re: Combining rows based on unique id, and combining information

This is one of my favourites :-)

 

1) Check column ID -> Mouse rightclick -> Unpivot Other Columns: This will delete the nulls

2) Check column Attribute -> Transform -> Any Column -> Pivot Column: Choose "Value" in Values Column

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




TBenders Regular Visitor
Regular Visitor

Re: Combining rows based on unique id, and combining information

Hi Imke, thanks so much for your help so far!

 

This seems to get me part of the way there, but the resulting values (after pivot) are only 0 or 1instead of the dates.

 

Example:

id

StartDate

   FollowupDate

     FinishedDate

101     

1

   1

   1

102

1

   0

   1

103

1

   0

   0

 

If I change type to date, the results are not accurate, all in 1899.

 

Does anyone know what I can try?

Super User
Super User

Re: Combining rows based on unique id, and combining information

Sorry, forgot to mention that you need to turn the aggregation off: last step: Advanced Opions: Don't aggregate bofore hitting the last OK.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




TBenders Regular Visitor
Regular Visitor

Re: Combining rows based on unique id, and combining information

Amazing, this did exactly what I was looking for. 

 

Thanks Imke!

Highlighted
RAdams Regular Visitor
Regular Visitor

Re: Combining rows based on unique id, and combining information

@ImkeF 

 

I am trying to accomplish something similar and when I hit Ok after selecting Don't Aggregate, my data returns Errors. 

 

When I click on the Error Link, it says "There were too many elements in the enumeration to complete the operation."

 

Any suggestions? 

 

Thanks!

R

 

 

Super User
Super User

Re: Combining rows based on unique id, and combining information

This is due to the fact that the column you've checked (where you say: "Unpivot others") would have duplicates after the operation / there would be more than 1 value in a cell.

I need to see a sample of your data to tell you how to overcome this. 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




EricC Frequent Visitor
Frequent Visitor

Re: Combining rows based on unique id, and combining information

Hi,

 

I may need to create a new post as this is quite a bit outdated but I am trying something similiar and need some assistance.

 

Here is my data:

 

UserNameUserEmailOfficeLoginDateLoginTimeViewedScreenTypeLocationShareholderLetterDateUniqueID
Smith, Johnname@company.com25/8/2018 0:0015:35:51Y    19523C53
     Y1FLWilliams, Gary5/8/2018 4:0019523C53
     Y1GASmith, John5/8/2018 4:0019523C53

 

 

 

I am using the patch/collect function in powerapps to spit this data to an excel workbook. It informs me if a user has viewed a screen, what selections they made in a dropdown etc.

My issue is as follows: A user may make multiple selections in the dropdowns and I need to track that data. (Type, Location, Shareholder & LetterDate)

 

As you can see in the data table above, it will collect Y for they viewed the screen, what drop down selections were made and a uniqueID for that specific session of them using the app. 

 

I'm wondering what the best way is for me to link that data to the UniqueID but still individually be able to track the different drop-down selections, but mash together the viewscreen columns since I don't care about the duplicate Ys so long as I have one Y, but I do need the specific selections from the dropdowns to report on.

Super User
Super User

Re: Combining rows based on unique id, and combining information

I would create 2 tables which will be connected in the data model via UniqueID:

 

1) Header

image.png

 

2) Details:

image.png

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




UCiccola Frequent Visitor
Frequent Visitor

Re: Combining rows based on unique id, and combining information

Hi Imkef,

 

I have de same problem. Some rows have error "There were too many elements in the enumeration to complete the operation."

I show you a picture of mi example. What do you need to help me?
error power bi.JPG

 

Hope you can help Me

 

Regards

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 335 members 4,210 guests
Please welcome our newest community members: