consolidating contact data

[circa]

Hi all, 

I am currently trying to work on consolidating some data they I then use forsome specific use cases and at the moment the way the data is provided weekly is a bit of a mess. there is only a limited number of rows with the same core information but then when it comes to contacts they seem to just add multiples i.e. shareholder 1, shareholder 2. 

I have tried to unpivot the data but that doesn't really work given the way I need to structure it. here is a dummy example of the way the data comes to me and a dummy of the way I need to structure it. Any advice or tips would be greatly appreciated!

how it comes: 

Premise ID

Key person

phone number

email

shareholder 1

shareholder 1 phone

shareholder 1 email

shareholder 1 shareholding

shareholder 2

shareholder 2 phone

shareholder 2 email

shareholder 2 shareholding

general manager

general manager email

general manager phone

There is about 7 or 8 other contact types

1

JohnSmith

6.12E+09

dummyemail1@dummyemail.com

madeup name

6.12E+09

dummyemail3@dummyemail.com

10%

example name

6.12E+09

dummyemail7@dummyemail.com

30%

Ithink yougetit

dummyemail9@dummyemail.com

6.12E+09

2

Jane Doe

6.12E+09

dummyemail2@dummyemail.com

random person

6.12E+09

dummyemail4@dummyemail.com

50%

struggling withnames

6.12E+09

dummyemail8@dummyemail.com

50%

last name

dummyemail10@dummyemail.com

6.12E+09

here is an example of what we would need:

Premise ID	Role	name	phone number	email	shareholder	shareholding
1	Key person	JohnSmith	6.12E+09	dummyemail1@dummyemail.com
2	Key person	Jane Doe	6.12E+09	dummyemail2@dummyemail.com
1		madeup name	6.12E+09	dummyemail3@dummyemail.com	y	10%
2		random person	6.12E+09	dummyemail4@dummyemail.com	y	50%
1		example name	6.12E+09	dummyemail7@dummyemail.com	y	30%
2		struggling withnames	6.12E+09	dummyemail8@dummyemail.com	y	50%
1	general manager	Ithink yougetit	dummyemail9@dummyemail.com	6.12E+09
2	general manager	last name	dummyemail10@dummyemail.com	6.12E+09

[lbendlin · Accepted solution]

You are right, unpivoting is what you need.  Alternatively you could create a couple of append scripts.  The shareholder holding column makes this non-trivial as it is not always present, so you would need to create a meta table that describes the number of columns for each subset - in your case 3 - 4 - 4 - 3  etc.

Contacts table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZG7CsMwDEV/xRg6NYQ46Stbh3Zo144hg2lEYmrZIbZp8/d1An1h3EVcEOdKV6oqymhCz7pTFxS283qTsvy4zEovG4c4AnIh2f6j06tG30TegOuJ4ggxqggpli18hQfHXsJfeBvCxQyfbCfUjYzatWCF/YHKEHqb10lF8yksV0AOOjo4Dz0GrhqNpIfBaBXjViG3nhc2dnBtK4Vqyd3feAptYia7mInkxr7u9fWX7F/g+gk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Premise ID" = _t, #"Key person" = _t, #"phone number" = _t, email = _t, #"shareholder 1" = _t, #"shareholder 1 phone" = _t, #"shareholder 1 email" = _t, #"shareholder 1 shareholding" = _t, #"shareholder 2" = _t, #"shareholder 2 phone" = _t, #"shareholder 2 email" = _t, #"shareholder 2 shareholding" = _t, #"general manager " = _t, #"general manager email" = _t, #"general manager phone" = _t])
in
    Source

"Key Person"  table (referenced from Contacts):

let
    Source = Contacts,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Premise ID", "Key person", "phone number", "email"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Key person", "name"}, {"phone number", "phone"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "role", each "Key person")
in
    #"Added Custom"

"Shareholder 1":

let
    Source = Contacts,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Premise ID", "shareholder 1", "shareholder 1 phone", "shareholder 1 email", "shareholder 1 shareholding"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"shareholder 1", "name"}, {"shareholder 1 phone", "phone"}, {"shareholder 1 email", "email"}, {"shareholder 1 shareholding", "shareholding"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "role", each "Shareholder 1")
in
    #"Added Custom"

and so on. Finally - append them together

let
    Source = Table.Combine({#"Key Person", #"Shareholder 1", #"Shareholder 2", #"General manager"})
in
    Source