Power Query Add New Columns According Qty of Phone Numbers

[Jose Fonyat]

Hello Guys!

Let me know if you could help me with this table below:

Id	Name	Phone Number	Index
7-100052	John	8523-1122	1
7-100052	John	9523-2233	2
4-150194	Richard	7532-1111	1
4-150194	Richard	8537-1020	2
4-150194	Richard	9321-6677	3

My goal is to add new columns named Phone Number 1, Phone Number 2 or Phone Number N as many phones as unique Ids we have and show their respective phone numbers.

Id	Name	Phone Number	Index	Phone 1	Phone 2	Phone 3
7-100052	John	8523-1122	1	8523-1122	9523-2233	null
7-100052	John	9523-2233	2	8523-1122	9523-2233	null
4-150194	Richard	7532-1111	1	7532-1111	8537-1020	9321-6677
4-150194	Richard	8537-1020	2	7532-1111	8537-1020	9321-6677
4-150194	Richard	9321-6677	3	7532-1111	8537-1020	9321-6677

After that I would just remove the duplicated rows.

Any help it would be very appreciated

Best Regards

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Name", type text}, {"Phone Number", type text}, {"Index", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Index", type text}}, "en-US")[Index]), "Index", "Phone Number")
in
    #"Pivoted Column"

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	Id	Name	1	2	3
2​	4-150194	Richard	7532-1111	8537-1020	9321-6677
3​	7-100052	John	8523-1122	9523-2233

Sheet: Sheet2

 

[AliGW]

Very easy!

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Name", type text}, {"Phone Number", type text}, {"Index", Int64.Type}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Index", each "Phone " & Text.From(_, "en-GB"), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Prefix",{"Id", "Name", "Index", "Phone Number"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Index]), "Index", "Phone Number")
in
    #"Pivoted Column"

Excel 2016 (Windows) 32 bit

	F​	G​	H​	I​	J​
1​	Id	Name	Phone 1	Phone 2	Phone 3
2​	4-150194	Richard	7532-1111	8537-1020	9321-6677
3​	7-100052	John	8523-1122	9523-2233

Sheet: Sheet1

Let me know if you need greater explanation.