Hi. I'm stuck again!!
I have a table of data that has a company name and ID, followed by up to 4 contacts on each row:
CompanyName, CompanyID, Firstname1, Lastname1, Occupation1, Firstname2, Lastname2, Occupation2, ....... Firstname4, Lastname4, Occupation4
I need to import the data into a database with company name and contact name and so the format needs to be :
CompanyName, CompanyID, Firstname, Lastname, Occupation
So there will be multiple (up to 4) rows for each company with a contact name on each row.
Can anyone help please?
I've attached a sample file to show input and output formats.
Thank you.
Robert
Please see attached. It's not a very neat solution but it works with the example you provided.
Start by unpivoting the table and tidy up a bit. Then add an index column and re-pivot. And then tidy upp again.
Thank you, again, Riny.
I started off by unpivoting but I got stuck with the result I got.
There is some M language in there that I am not familier with. I can follow it through except I am unsure of the two Text.Trim commands in lines 3 and 4 after the Unpivot. Would you mind explaining those steps please?
Cheers
R
Hi Robert,
All was done by clicking in the user interface. The Trim 'function' sit in the Transform tab under Format. All it does is get rid of leading and trailing spaces in a field.
Note that the texts in the Attribute and Value columns aren't lined up neatly. Trim fixes that.
Gotcha.
Thank you again. Really great.
Cheers
R
