Processing Excel Mailing Label Output with Power Query Editor

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