Reply
Regular Visitor
Posts: 37
Registered: ‎03-16-2017
Accepted Solution

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:

 

headersdata
name,surname,address,city,phoneJohn,Smith,Baker st. 21B,London,3758
name,surname,address,city,phoneCarol,Newman,Picadilly 23,London,5663
name,surname,address,city,phoneBernard,Collins,7th Avenue 432,NYC,3456

 

and obviously what I would like would be something like:

 

namesurnameaddresscityphone
JohnSmithBaker st. 21BLondon3758
CarolNewmanPicadilly 23London5663
BernardCollins7th Avenue 432NYC3456

 

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!


Accepted Solutions
Highlighted
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Sorting header and data columns separated by commas

in Query editor you can add custom column like this

Record.FromList(Text.Split([data], ","), Text.Split([headers], ","))

and then expand the new column

View solution in original post


All Replies
Highlighted
Super User
Posts: 866
Registered: ‎06-23-2016

Re: Sorting header and data columns separated by commas

in Query editor you can add custom column like this

Record.FromList(Text.Split([data], ","), Text.Split([headers], ","))

and then expand the new column

Regular Visitor
Posts: 37
Registered: ‎03-16-2017

Re: Sorting header and data columns separated by commas

Perfect! Thanks!