cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TBenders
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

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

20 REPLIES 20
Benarfa
Regular Visitor

HI there, I have similar prolem. I could find the post for the solution. Could you please repost the solution 

Shrutisakpal
New Member

Hi Imke,

 

I tried your solution for one of my data which i need in same format.

 

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

 

But its giving error "There were too many elements in the enumeration to complete the operation.". Can you please help?

 

Regards,

Shruti

That's because your table is missing a unique identifier that determines the row.

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

Imke - perhaps you can help me with a similar situation. I am also trying to combine data like the previous users but mine would be by date and I want the sum of the numbers instead of just a count. It looks like the aggregate only gives a count? 

 

The situation is multiple locations entering data into different tables on the same date and I would like to combine those into a global sum for each attribute being tracked while having accurate monthly/quarterly numbers.

 

ImkeF
Super User II
Super User II

Hi @kohrt ,

Not sure I can follow. Could you please share (link to) some sanitzed sample data ?

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

Hi all, I am having very similar problems to the above, as you can see below the Id numbers are sorted in order and there is data in different columns:

Capture.PNG

Then when you follow the process mentioned.... After the first step, something weird happens:

 

Capture1.PNG

Then when you apply the second stage they all become errors:

Capture2.PNG

It feels like there is something really simple to fix here?

ImkeF
Super User II
Super User II

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

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?

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

@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

 

 

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

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

Hi @UCiccola ,

These errors mean that you're trying to bring data into one row where there's actually more than one value per pivoted column. So you have to make up your mind and decide what to do with these multipe values: Combine then into one or delete them, because there are errors, create a separater table ... whatever is appropriate for your specific case.

 

To visualize the problem, you can add the following code as the 5th parameter to your Table.Pivot-function:

 

each Text.Combine(_, "#(lf)Additional Item: ")

This will create a line feed and a starting phrase for every multiple item in these rows.

 

 

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

Hi @ImkeF 

I know. If you want i can show you the file.pbx. These duplicate values are the "Titles" i want to have for the columns. The thing is that if i eliminate those values from the column "Attribute",it also eliminates the row of the column "Value". Sorry for my english. I show you what i need to have:

LegajoEstilo PersonalÁrea de Mejora
145informationinformation
567informationinformation
456informationinformation
546informationinformation

 

And i show you what i have:

error power bi.JPG

As you see, the information is a bit messed up. This is because it is import from pdf files which are interviews from employees.

I take your help to know if you know a better way to import files from this source (pdf files) since they complicate my work a lot.

Thanks in advance


Regards 

Sorry, but I cannot find any clarification in you latest post.

Please keep your samples to the first 3 "Legajo"s: 154, 219 and 305:

 

image.png

 

  1.  What do they contain? (You can use the formula I've suggested, but don't have to. Otherwise just paste the raw data)
  2.  How do you want to visualize them?

The source of your data is irrelevant at this stage.

 

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

The error occurs because the titles "Estilo Personal" and "Áreas de Mejora" appears several times. I show you the difference between the error and the rows OK:
Before pivot:
Error example: 503
Error Power bi 2.JPG
Example OK: 450
Erorr PowerBI.JPG

As you can see the difference are the duplicates of the titles. But as i said if i eliminate them it will eliminate all the info of the column "Value"

 

Thanks

If you add 

each Text.Combine(_, "#(lf)") 

as the 5th parameter to your Table.Pivot function, you'll get all the different rows combined with a line break in your pivot table.

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

EricC
Frequent Visitor

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.

ImkeF
Super User II
Super User II

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

Amazing, this did exactly what I was looking for. 

 

Thanks Imke!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors