Hi, I get raw data in report format that looks like this: Customer: 78566 Address: BOX HILLHOSPITAL EASTERNHEALTH-ACCTS PAYABLE 8 ARNOLDSTREET BOX HILLVIC3128 AU MaterialDescriptionList priceCurrCustomer priceCurrDeal type 9300633714444WOOLWORTHS TUNA IN SWEET CHILLI SAUCE 95G0.885AUD0.797AUDSold-to Contr Price0066613004026BRUNSWICK SARDINES IN OLIVE OIL 106G0.889AUD0.801AUDSold-to Contr Price Please can you help me reformat the header so my output data looks like this. Thank you. CustomerMaterialDescriptionList priceCurrCustomer priceCurrDeal type785669300633714444WOOLWORTHS TUNA IN SWEET CHILLI SAUCE 95G0.885AUD0.797AUDSold-to Contr Price785660066613004026BRUNSWICK SARDINES IN OLIVE OIL 106G0.889AUD0.801AUDSold-to Contr Price Customer Price List Example.xlsx

Help with processing data from a report that contains unwanted headers

Hi,

I get raw data in report format that looks like this:

Customer: 78566
Address: BOX HILL	HOSPITAL
EASTERN	HEALTH-ACCTS PAYABLE
8 ARNOLD	STREET
BOX HILL	VIC3128
AU


Material	Description	List price	Curr	Customer price	Curr	Deal type

9300633714444	WOOLWORTHS TUNA IN SWEET CHILLI SAUCE 95G	0.885	AUD	0.797	AUD	Sold-to Contr Price
0066613004026	BRUNSWICK SARDINES IN OLIVE OIL 106G	0.889	AUD	0.801	AUD	Sold-to Contr Price

Please can you help me reformat the header so my output data looks like this. Thank you.

Customer	Material	Description	List price	Curr	Customer price	Curr	Deal type
78566	9300633714444	WOOLWORTHS TUNA IN SWEET CHILLI SAUCE 95G	0.885	AUD	0.797	AUD	Sold-to Contr Price
78566	0066613004026	BRUNSWICK SARDINES IN OLIVE OIL 106G	0.889	AUD	0.801	AUD	Sold-to Contr Price

Customer Price List Example.xlsx

Datasets

Accepted answers

Ben_H

Hi @chc9,

I've had a go at this - it assumes that the structure of your input is going to be generally the same going forward.

The first thing I've done here is try to identify the specific rows we want, so I've added a record ID first.

Then I've tranposed the data and done two things

1. Identify the rows that contain "Customer:" to get the ID we need

2. Count the number of non-null fields in each row - the assumption being that the max non-null count will be the table rows.

I then join back to the main stream, just the rows that have useful data in.

Create a column for customer ID and fill it down using a multirow formula.

I then update the column names using a dynamic replace and filter out a repeat of the column names later (your input had two tables with the same structure) to give the following output

Hope that helps,

Regards,

Ben

Reformat Input.yxzp

All comments

apathetichell

Are you looking for something like this? It takes away the header records and isolates the field names. It then uses dynamic rename to turn them into the column headers...

Hope this helps.

alteryx field rename solution.yxmd

chc9

Thanks!

I ran your workflow using my input data from the excel and the output failed.

Pls can you have another look using my excel example data?

Thanks!

Customer Price List Example.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335