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

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

19 REPLIES 19
Highlighted
Super User IV
Super User IV

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper II
Helper II

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?

Highlighted
Super User IV
Super User IV

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper II
Helper II

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

Amazing, this did exactly what I was looking for. 

 

Thanks Imke!

Highlighted
Helper III
Helper III

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

 

 

Highlighted
Super User IV
Super User IV

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. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
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.

Highlighted
Super User IV
Super User IV

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper I
Helper I

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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors