Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Id | numbervalue 1 | numbervalue 2 | measurement1 | measurement2 | freetext1 | freetext2 |
3 | 1 | null | null | null | ||
3 | null | 0 | 15 | null | this is hard | |
3 | null | null | null | 12 | ||
3 | null | null | null | null | it should be easier | |
4 | 1 | null | null | null | ||
4 | null | 2 | 45 | null | more notes | |
4 | null | null | null | 16 |
and my desired output is:
id | numbervalue 1 | numbervalue 2 | measurement1 | measurement2 | freetext1 | freetext2 |
3 | 1 | 0 | 15 | 12 | this is hard | it should be easier |
4 | 1 | 2 | 45 | 16 | more 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
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |