Log In
Register
Home
November 15, 2017
Offline
I have this text file which contains: Name; Address; City, State, and Zip Code. I am trying to make a database to make a mailing list. I am trying to transpose the data into a column for the following:
1st column: Mr. and Mrs.
2nd column: John (first name)
3rd column: Doe (last name)
4th column: address
5th column: City
6th column: State
7th column: Zip code
Is this possible using Power Query? Please see attached text file.
I would really appreciate anyone's help.
Thanks,
Ken Mc
July 16, 2010
Offline
Hi Ken,
Yes, you can unpivot the data. First remove the blank rows in column1, then add an index column. Convert the Index to Modulo and Pivot based on the modulo column.
You can see it explained here in example 4: https://www.myonlinetraininghu.....-scenarios
I would have provided you with a sample file, but my Office Insiders version of Excel has a bug preventing me from pivoting!
Mynda
November 15, 2017
Offline
Thank you very much Mynda! I will give that a shot and will let you know.
Best,
Kenny Mc
November 15, 2017
Offline
Mynda,
I'm running into a problem with my column of data. Before I add an index column and convert to Modulo, I need the list column broken down like this:
A row for each of the following
Row for:
Salutation
First Name
Last Name
Address(which already has it's own row
City
State
Zip Code
I don't know how to accomplish that in Power Query.
I hope you can help. Please see attached file I have done so far.
Thanks so much,
Ken Mc
VIP
Trusted Members
December 7, 2016
Offline
Hello Kenneth,
I try to give you an answer as this is gives me a good training in using Power Query.
The steps I used is visible when you choose to view the query in the attached file.
I choosed not to split the salutation, first and last names to their own columns, as the data differs in your list, in one row there are for example no salutation but two names. But now when you have it all in an Excel table that part is easy to fix.
November 15, 2017
Offline
Never mind Mynda, I figured out the rest.
Thanks very much for your help!!
Regards,
Kenny Mc
November 15, 2017
Offline
Very nice Anders.
My sister wanted the Salutation, so I came up with the exact same solution thanks to Mynda's solution. Here is my finished file.
I'm glad this will help in your training.
Best,
Kenny Mc
1 Guest(s)
