Forum Discussion
Sorting header and data columns separated by commas
This might be quite easy to solve but I'm stuck with this for a while now.
I've got this "simple" estructure coming from a XML file:
| headers | data |
| name,surname,address,city,phone | John,Smith,Baker st. 21B,London,3758 |
| name,surname,address,city,phone | Carol,Newman,Picadilly 23,London,5663 |
| name,surname,address,city,phone | Bernard,Collins,7th Avenue 432,NYC,3456 |
and obviously what I would like would be something like:
| name | surname | address | city | phone |
| John | Smith | Baker st. 21B | London | 3758 |
| Carol | Newman | Picadilly 23 | London | 5663 |
| Bernard | Collins | 7th Avenue 432 | NYC | 3456 |
Now take in consideration that we're talking about millions of rows and there are more headers than in the example, so performance matters here. Please, could anybody provide any tips? Thanks!
in Query editor you can add custom column like this
Record.FromList(Text.Split([data], ","), Text.Split([headers], ","))
and then expand the new column
2 Replies
in Query editor you can add custom column like this
Record.FromList(Text.Split([data], ","), Text.Split([headers], ","))
and then expand the new column