excel convert data into multi rows from single row-CodePudding

I have below sample insurance data contains family details in single row for each ID.

ID      Enrollment date  Area   Full Name   Gender  DOB         Sum Insured Spouse Name  Gender DOB         Kid1_Name   Gender  DOB         Kid2_Name   Gender  DOB
29348   24-01-2021       17     NAINAR  M   Male    17-Mar-1982 500000      SUBBULAKSHMI FEMALE 21-Jun-1988 GOKULSRIRAM MALE    31-Oct-2007 SRIDHAR     MALE    19-Feb-2009
23434   19-04-2020       17     Kishore     Male    12-Jun-1986 200000       A Savitha   Female 10-Jun-1991 Sathvik     Male    4-Mar-2014  A Saketh    male    13-Feb-2015
46565   01-05-2020        5     Ragu        Male    6-Aug-1996  300000

I'm trying to convert data like below format, so that family details are shown in rows

Tried using PivotTable option and power query option in excel but no luck.

Is it possible in excel ?

Thanks

CodePudding user response：

Here's one kludgy way to do it in powerquery

(a) Merge groups of columns together (b) unpivot (c) split those columns again

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Self", each "Self"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Spouse", each "Spouse"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Child1", each "Child1"),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Child2", each "Child2"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom3", {{"DOB", type text}, {"Sum Insured", type text}}, "en-US"),{"Full Name", "Gender", "DOB", "Self","Sum Insured"},Combiner.CombineTextByDelimiter("::", QuoteStyle.None),"m1"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"DOB3", type text}}, "en-US"),{"Spouse Name", "Gender2", "DOB3", "Spouse"},Combiner.CombineTextByDelimiter("::", QuoteStyle.None),"m2"),
#"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"DOB5", type text}}, "en-US"),{"Kid1_Name", "Gender4", "DOB5", "Child1"},Combiner.CombineTextByDelimiter("::", QuoteStyle.None),"m3"),
#"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"DOB7", type text}}, "en-US"),{"Kid2_Name", "Gender6", "DOB7", "Child2"},Combiner.CombineTextByDelimiter("::", QuoteStyle.None),"m4"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns3", { "ID", "Enrollment date", "Area"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("::", QuoteStyle.Csv), {"Name", "Gender", "Date of Birth", "Relation", "Insured amount"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each ([Name] <> "")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date of Birth", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date of Birth", type date}, {"Enrollment date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute"})
in  #"Removed Columns"

Note if you load multiple columns with same column headers into powerquery, then the titles will change to have numbers after them. You probably will have to update code to fix the column names for Date Birth and Gender

CodePudding user response：

Here is another power query method.

Original Data

Read the code comments and explore the Applied steps to get a better idea of the algorithm.

Select the ID column and Unpivot other columns
Group by ID
Create a custom aggregation that creates a List of records for each family
Expand the records into a table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    //Unpivot all except ID column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),

//Group by ID then custom aggregation
    //Column Names for final report
    colNames = {"Date of Enrollment", "Area", "Relation", "Name", "Gender", "Date of Birth", "Sum Insured"},
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"ID"}, {
        {"Records", (t)=>
            List.Generate(
                ()=>[ed=t[Value]{0}, a=t[Value]{1}, r="Self", n=t[Value]{2}, g=t[Value]{3}, dob=t[Value]{4}, si=t[Value]{5}, idx=5],
                each [idx] < Table.RowCount(t),
                each [ed=null, a=null, r=Text.SplitAny(t[Attribute]{[idx] 1}," _"){0}, 
                n=t[Value]{[idx] 1}, g=t[Value]{[idx] 2}, dob=t[Value]{[idx] 3}, si=null, idx=[idx] 3],
                each Record.FromList(
                    {[ed],[a],[r],[n],[g],[dob],[si]},
                    colNames)
            )}}),

    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"ID"}),
    #"Expanded Records" = Table.ExpandListColumn(#"Removed Columns", "Records"),
    #"Expanded Records1" = Table.ExpandRecordColumn(#"Expanded Records", "Records", 
        colNames,colNames),
    
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Records1",{
        {"Date of Enrollment", type date}, {"Area", Int64.Type}, {"Relation", type text}, {"Name", type text}, 
        {"Gender", type text}, {"Date of Birth", type date}, {"Sum Insured", Currency.Type}})
in
    #"Changed Type1"

Results