Processing Excel Mailing Label Output with Power Query Editor

[Anonymous]

I was provided with some data in Exel (xlsx) with the following repeating format:

1. Name
2. Address
3. City, State, Zip, Country (when not Address 2)
4. City, State Zip, Country (when Address 2 is not empty, otherwise null)

I'd like to transpose each block of 4 such that each row is a record.

What steps would you use to do this in Power Query Editor?

[Anonymous]

Also, to split the City/State/Zip, in Excel I used Text to Columns with the comma delmiter to convert:

City, State Zip Country

to

City | State Zip Country

(Where | indicates a different cell)

Then I used Text to Columns again with a space delimiter to break these out... I forget how to do that in Power Query Editor, but I know it's possible, so I'll note that here when I find it.

[Anonymous]

In Excel I would do this by pasting the following query in field B1, then dragging to the right 4 cells:

=INDEX($A:$A,ROW(A1)*4-4+COLUMN(A1))

This handles the transposition...
Next steop is to create a field that ensures that the 4th column always includes City/State/Zip.

...but how would I do this in the Power Query Editor so I could just update the Excel file with a fresh one and have these changes applied automatically?

[Jimmy801]

Hello @Anonymous 

could you please post an exampleof your table and the expected result.

Jimmy

[Anonymous]

---

@Jimmy801 wrote:

Hello @Anonymous 

could you please post an exampleof your table and the expected result.

Jimmy

---

Example table:

John Doe

123 Main St

Lewis Center, OH 43035 USA

Santa Claus

1 North Pole Dr

Groveport, OH 43125 USA

Easter Bunny

15 Basket Ct

Apt 3B

Columbus, OH 43202 USA

Expected result:

Name	Address	Address2	City, State ZIP Country
John Doe	123 Main St		Lewis Center, OH 43035 USA
Santa Claus	1 North Pole Dr		Groveport, OH 43125 USA
Easter Bunny	15 Basket Ct	Apt 3B	Columbus, OH 43202 USA

[Jimmy801 · Accepted solution]

Hello @Anonymous 

check out this solution.

It applies a Table.Split of 4 row each. Afterwards a List.Transform is applied to check how man nonempty rows are present and depending on the rows found a Table.Transpose is applied. Here the complete code

let
	Source = #table
	(
		{"Data"},
		{
			{"John Doe"},	{"123 Main St"},	{"Lewis Center, OH 43035 USA "},	{""},	{"Santa Claus"},	{"1 North Pole Dr"},	{"Groveport, OH 43125 USA "},	{""},	{"Easter Bunny"},	
			{"15 Basket Ct"},	{"Apt 3B"},	{"Columbus, OH 43202 USA "}
		}
	),
    Split = Table.Split
    (
        Source,
        4
    ),
	Transform = List.Transform
	(
		Split,
		each 
		if Table.RowCount(Table.SelectRows(_, each [Data]<>"")) = 4 then 
		Table.Transpose
		(
			_,
			{"Name", "Address", "Address2", "City, State ZIP Country"}
		)
		else 
		Table.Transpose
		(
			Table.SelectRows(_, each [Data]<>""),
			{"Name", "Address", "City, State ZIP Country"}
		)
	),
	Combine = Table.Combine
	(
		Transform
	)
in
	Combine

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy