cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tmadams Frequent Visitor
Frequent Visitor

flatten multiple rows by unique id with number and text columns

Greetings,

 

I've searched through the forums quite extensively and while I've gotten closer to a solution I appear to be stuck.

 

Here's a sample of what my data looks like:

 

Idnumbervalue 1numbervalue 2measurement1measurement2freetext1freetext2
31nullnullnull  
3null015nullthis is hard 
3nullnullnull12  
3nullnullnullnull it should be easier
41nullnullnull  
4null245nullmore notes 
4nullnullnull16  

 

and my desired output is:

 

idnumbervalue 1numbervalue 2measurement1measurement2freetext1freetext2
3101512this is hardit should be easier
4124516more notes 

 

following this solved example here and using the recommendation from ImkeF (as the coding was less and I could mostly understand it) I am able to successfully get all of my number values but I lose all of the text. I've also tried to unpivot/pivot with no aggregation but again, I lose all my text to the error: "there are too many elements in the enumeration".

 

My full dataset has approximately 90 columns with 4 of them being free text fields for unique entry.

 

Any help is appreciated!

 

Thanks,

Todd

1 ACCEPTED SOLUTION

Accepted Solutions
tmadams Frequent Visitor
Frequent Visitor

Re: flatten multiple rows by unique id with number and text columns

I stepped away from power query for a  few hours and then came back to it with a fresh mind. I decided to attempt something that I thought might work and turns out it did. 

 

After unpivoting the data I knew that I would have 2-4 iterations of each of my headers per record_id. In the Values column that got created I filtered out everything with a value of 'Blank' to leave only those fields with a number or text. I guessed that I would have a value for every column across all of my rows and that proved to be true. with all of the previously blank text fields now gone, when I pivot the data only the single free text instance exists so it has no problem returning that appropriately.

 

This certainly isn't the most elegant solution but for this instance it happens to work. If anyone has other methods for a solution that are more robust I would be more than happy to hear about it but for now my problem happens to be solved.

1 REPLY 1
tmadams Frequent Visitor
Frequent Visitor

Re: flatten multiple rows by unique id with number and text columns

I stepped away from power query for a  few hours and then came back to it with a fresh mind. I decided to attempt something that I thought might work and turns out it did. 

 

After unpivoting the data I knew that I would have 2-4 iterations of each of my headers per record_id. In the Values column that got created I filtered out everything with a value of 'Blank' to leave only those fields with a number or text. I guessed that I would have a value for every column across all of my rows and that proved to be true. with all of the previously blank text fields now gone, when I pivot the data only the single free text instance exists so it has no problem returning that appropriately.

 

This certainly isn't the most elegant solution but for this instance it happens to work. If anyone has other methods for a solution that are more robust I would be more than happy to hear about it but for now my problem happens to be solved.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 242 members 3,340 guests
Please welcome our newest community members: