Making a new row for each set of columns that are the same data

[cah2035]

Hello, I have a large table where "child" records exist in a single row and I am trying to make them into their own rows. See the example below:

I want to take this:

Name	Child Name	Child DOB	Child Name 2	Child DOB 2
John	Peter	3/2/2000	Martha	9/14/2006
Samantha	Mary	12/20/1999	Jennifer	11/2/1986

And turn it into this:

Name	Child Name	Child DOB
John	Peter	3/2/2000
John	Martha	9/14/2006
Samantha	Mary	12/20/1999
Samantha	Jennifer	11/2/1986

I know how to do it in a long frustrating way, by extracting each set into its own table and then append them, but I am sure there is a much easier way to do this. I have 6 sets of "Child" columns in total so figuring out a function to make it a quick process would be helpful. 

Thanks!

[CNENFRNL · Accepted solution]

@cah2035 , you might want to try the following methods, one of which is merely clicks on functionalities at UI, while another one is to use functions by yourself.

UI solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpILUktAtLG+kb6RgYGBkCmb2JRSUYikGGpb2gCEjRTitWJVgpOzE3Mg0gAVVQCKUOQFn1DS0tLIMcrNS8vMw1slqEh0DBDSwugvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Child Name" = _t, #"Child DOB" = _t, #"Child Name 2" = _t, #"Child DOB 2" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, " ", 1), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Child Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Child DOB] <> null))
in
    #"Filtered Rows"

Self-defined steps,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpILUktAtLG+kb6RgYGBkCmb2JRSUYikGGpb2gCEjRTitWJVgpOzE3Mg0gAVVQCKUOQFn1DS0tLIMcrNS8vMw1slqEh0DBDSwugvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Child Name" = _t, #"Child DOB" = _t, #"Child Name 2" = _t, #"Child DOB 2" = _t]),
    Custom1 = Table.ToRows(Source),
    Custom2 = List.Transform(
        Custom1, 
        each 
        let 
            p = _{0}, l = List.Skip(_), 
            m = List.Split(l,2), 
            n = List.Transform(m, each {p}&_) 
        in 
            Table.FromRows(n, {"Name","Child Name","DOB"})
    ),
    Custom3 = Table.Combine(Custom2)
in
    Custom3

[edhans · Accepted solution]

Hi @cah2035 - you can try this. It does an unpivot operation, then fills the dates up for the DOB, then filters out the NULLs in the child's name. Partway through it looks like this:

Then just do the fill up operation on the DOB, Filter nulls, and remove the index column. You are left with this.

The temporary INDEX column is needed for the pivot operation. You can see the code here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpILUktAtLG+kb6RgYGBkCmb2JRSUYikGGpb2gCEjRTitWJVgpOzE3Mg0gAVVQCKUOQFn1DS0tLIMcrNS8vMw1slqEh0DBDSwugvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Child Name" = _t, #"Child DOB" = _t, #"Child Name 2" = _t, #"Child DOB 2" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, " ", 1), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"Child DOB"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Child Name] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Child Name", "Child DOB"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Child DOB", type date}})
in
    #"Changed Type"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[CNENFRNL · Accepted solution]

@cah2035 , you might want to try the following methods, one of which is merely clicks on functionalities at UI, while another one is to use functions by yourself.

UI solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpILUktAtLG+kb6RgYGBkCmb2JRSUYikGGpb2gCEjRTitWJVgpOzE3Mg0gAVVQCKUOQFn1DS0tLIMcrNS8vMw1slqEh0DBDSwugvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Child Name" = _t, #"Child DOB" = _t, #"Child Name 2" = _t, #"Child DOB 2" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, " ", 1), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Child Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Child DOB] <> null))
in
    #"Filtered Rows"

Self-defined steps,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpILUktAtLG+kb6RgYGBkCmb2JRSUYikGGpb2gCEjRTitWJVgpOzE3Mg0gAVVQCKUOQFn1DS0tLIMcrNS8vMw1slqEh0DBDSwugvlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Child Name" = _t, #"Child DOB" = _t, #"Child Name 2" = _t, #"Child DOB 2" = _t]),
    Custom1 = Table.ToRows(Source),
    Custom2 = List.Transform(
        Custom1, 
        each 
        let 
            p = _{0}, l = List.Skip(_), 
            m = List.Split(l,2), 
            n = List.Transform(m, each {p}&_) 
        in 
            Table.FromRows(n, {"Name","Child Name","DOB"})
    ),
    Custom3 = Table.Combine(Custom2)
in
    Custom3

[HotChilli]

Select the Name column.

Choose 'Unpivot other columns'.

Select the Attribute column.

Split the column by delimiter (from non-digit to digit).

Select the attribute1 column and do a 'Clean' and probably 'Trim' too.  This makes sure all spaces are removed.

Pivot the attribute1 column-> Use value in the Values, and choose 'Don't aggregate' from advanced section.

Good luck