How can I split a column of data into 2 columns, based on the value of another column

[Sylbaryn]

Hi.

My data only has two columns. A Product Number and a Customer Number. Each product must have either one or two associated customers and a customer can have as many products as they want. My aim is to split the Customer Number column into Customer 1 and Customer 2 columns (Customer 2 column to be blank if there isn't a second customer). But based on the product Number, so we only ever have one row for each product number. 

Is this possible in power query? I've been messign about pivoting and unpivoting but can't find a solution. 

Any help would be most appreciated. Thank you. 

Go from the first table to the second. 

[ronrsnfld · Accepted solution]

You can use the Table.Group function. The code below assumes that there is one or two customers per Product. If there might be more, the code can be rewritten to handle any number of customers.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJVMDpVgdJK4hnGsI4hrBuUYgrjEq1wTONQZxTVG5ZnCuCSrXFNUoU6hRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Num..." = _t, #"Customer Nu..." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Num...", Int64.Type}, {"Customer Nu...", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Num..."}, {
        {"Customer 1", each [#"Customer Nu..."]{0}, type nullable text},
        {"Customer 2", each try [#"Customer Nu..."]{1} otherwise null, type nullable text}
    })
in
    #"Grouped Rows"

[ThxAlot]

For arbitary count of columns,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJVMDpVgdJK4hnGsI4hrBuUYgrjEq1wSFa2lpic5HCBiD1Juics3gXBNUrimqXaZQu2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Number" = _t, #"Customer Number" = _t]),
    #"Grouped by Prod Num" = Table.Group(Source, "Product Number", {"Prod", each [Customer Number]}),
    Columns = {#"Grouped by Prod Num"[Product Number]} & List.Zip(#"Grouped by Prod Num"[Prod]),
    #"To Table" = Table.FromColumns(Columns)
in
    #"To Table"

[Sylbaryn]

Thank you very much

[ronrsnfld · Accepted solution]

You can use the Table.Group function. The code below assumes that there is one or two customers per Product. If there might be more, the code can be rewritten to handle any number of customers.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAiUdJVMDpVgdJK4hnGsI4hrBuUYgrjEq1wTONQZxTVG5ZnCuCSrXFNUoU6hRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Num..." = _t, #"Customer Nu..." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Num...", Int64.Type}, {"Customer Nu...", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Num..."}, {
        {"Customer 1", each [#"Customer Nu..."]{0}, type nullable text},
        {"Customer 2", each try [#"Customer Nu..."]{1} otherwise null, type nullable text}
    })
in
    #"Grouped Rows"

[Sylbaryn · Accepted solution]

Thank you so much. I can pretend to understand at first glance but I'll be delving into your suggestion. Thanks again