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

View solution in original post

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 373 members 3,884 guests
Please welcome our newest community members: