Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tmadams
Regular 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
tmadams
Regular Visitor

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
tmadams
Regular Visitor

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.