Forum Discussion

Sylbaryn's avatar
Sylbaryn
New Member
2 years ago
Solved

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

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. 

 

 

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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's avatar
    Sylbaryn
    2 years ago

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

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Sylbaryn
      New Member
      2 years ago

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

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    2 years ago

    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"