Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Processing Excel Mailing Label Output with Power Query Editor

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?

1 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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

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
Community Champion
Community Champion

Hello @Anonymous 

 

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

 

Jimmy

 

Anonymous
Not applicable


@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:

NameAddressAddress2City, State ZIP Country
John Doe123 Main St Lewis Center, OH 43035 USA 
Santa Claus1 North Pole Dr Groveport, OH 43125 USA 
Easter Bunny15 Basket CtApt 3BColumbus, OH 43202 USA 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors