Forum Discussion
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?
Sorry, forgot to mention that you need to turn the aggregation off: last step: Advanced Opions: Don't aggregate bofore hitting the last OK.
22 Replies
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
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.
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 - 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.
I had the same problem but i solved it by adding this after step 1:
1) Check column ID -> Mouse rightclick -> Unpivot Other Columns: This will delete the nulls
1.1) Select Column ID and Attribute together -> mouse right click -> remove duplicates
2) Check column Attribute -> Transform -> Any Column -> Pivot Column: Choose "Value" in Values Column (Advanced Options: Don't aggregate bofore hitting the last OK.)
