Transforming data as indicated

[enoch99]

Hello everyone, 

I have a table like the one below, and I wanted to tranform it to two different structucture. The sample of original table is:

1. First I want to tranform the table to:

2. Then I want another table further transormed as below:

Thank you in advance, and I greatly appreciate any kind of support.

[ronrsnfld · Accepted solution]

To create the first table:

Data

Month	Place	Engineer	Girls<17	Boys <17	Women Adult	Men Adult	Elderly Women	Elderly Men	Doctor	Girls<172	Boys <173	Women Adult4	Men Adult5	Elderly Women6	Elderly Men7	Teacher	Girls<178	Boys <179	Women Adult10	Men Adult11	Elderly Women12	Elderly Men13
January	Place A	Yes	2	0	1	4	1	1	Yes	0	0	1	2	3	2	Yes	1	2	2	3	1	5
January	Place B	No	0	0	0	0	0	0	Yes	0	0	0	2	3	1	Yes	1	3	4	4	5	6
February	Place A	Yes	0	0	2	1	0	0	No	0	0	0	0	0	0	Yes	0	0	0	0	0	1

let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Month/Place/Profession" = List.Alternate(Table.ColumnNames(Source),6,1,3),
    #"Base Attributes" = List.FirstN(List.RemoveFirstN(Table.ColumnNames(Source),3),6),

//Replace nulls with 0's to retain all rows when we unpivot
    #"Replace Null with 0" = Table.ReplaceValue(Source,null,0,
        Replacer.ReplaceValue,List.RemoveMatchingItems(Table.ColumnNames(Source),#"Month/Place/Profession")),
    unPivot = Table.UnpivotOtherColumns(#"Replace Null with 0", {"Month","Place"},"Attribute","Value"),

//Add column just for Professions
    #"Added Custom" = Table.AddColumn(unPivot, "Profession", each if [Value]="Yes" or [Value]="No" then [Attribute] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Profession"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "No" and [Value] <> "Yes")),

//Remove the trailing numbers added to column names to prevent duplicate column names in original Source table
    #"Attribute Translation" = List.Zip({
            #"Filtered Rows"[Attribute],
            List.Repeat(#"Base Attributes", Number.IntegerDivide(Table.RowCount(#"Filtered Rows"),6))
            }),
    #"Rename Attributes" = Table.TransformColumns(#"Filtered Rows",{
        {"Attribute", (x)=> List.Select(#"Attribute Translation", each _{0}=x){0}{1}}
        }),

//Group and then Pivot each subgroup
    #"Grouped Rows" = Table.Group(#"Rename Attributes", {"Month", "Place", "Profession"}, {
        {"Pivot", each Table.Pivot(_, [Attribute], "Attribute","Value")}}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Grouped Rows", "Pivot", #"Base Attributes"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Pivot",
        List.Zip({Table.ColumnNames(#"Expanded Pivot"), List.Repeat({type text},3) & List.Repeat({Int64.Type},6)}))
in
    #"Changed Type"

Results

Second Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Month/Place/Profession" = List.Alternate(Table.ColumnNames(Source),6,1,3),
    #"Base Attributes" = List.FirstN(List.RemoveFirstN(Table.ColumnNames(Source),3),6),
    #"Replace Null with 0" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,List.RemoveMatchingItems(Table.ColumnNames(Source),#"Month/Place/Profession")),
    unPivot = Table.UnpivotOtherColumns(#"Replace Null with 0", {"Month","Place"},"Attribute","Value"),
    #"Added Custom" = Table.AddColumn(unPivot, "Profession", each if [Value]="Yes" or [Value]="No" then [Attribute] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Profession"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "No" and [Value] <> "Yes")),
    #"Add Sex" = Table.AddColumn(#"Filtered Rows","Sex", each if
        Text.Contains([Attribute],"Girl") or Text.Contains([Attribute],"Women") then "F" else "M",type text),
    #"Add Age" = Table.AddColumn(#"Add Sex", "Age", each 
        if Text.Contains([Attribute], "<") then "0-17"
        else if Text.Contains([Attribute],"Elderly") then "60+"
        else "18-59", type text ),
    #"Removed Columns" = Table.RemoveColumns(#"Add Age",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Month", "Place", "Profession", "Sex", "Age"}, {{"Number", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

2nd table results, filtered to show only January / Place A / Engineer

[dufoq3 · Accepted solution]

Hi @enoch99, due to @ronrsnfld provided sample data in usable format I've played with it for a while. It was a good practice with list transformations 😉

Task1 Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUQrISUxOVXAEsiJTi4GkERAbALEhEJtAaUO4rAGSLEilMZSGyMJEYTIgvqlSrA6mfU5All8+koHoGNU6AzRDEdYZQ51pArZKR8kMbJ1balIRDv8hG2eIxCfRNYiAiI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Place = _t, Engineer = _t, #"Girls<17" = _t, #"Boys <17" = _t, #"Women Adult" = _t, #"Men Adult" = _t, #"Elderly Women" = _t, #"Elderly Men" = _t, Doctor = _t, #"Girls<172" = _t, #"Boys <173" = _t, #"Women Adult4" = _t, #"Men Adult5" = _t, #"Elderly Women6" = _t, #"Elderly Men7" = _t, Teacher = _t, #"Girls<178" = _t, #"Boys <179" = _t, #"Women Adult10" = _t, #"Men Adult11" = _t, #"Elderly Women12" = _t, #"Elderly Men13" = _t]),
    ColNames = Table.ColumnNames(Source),
    Professions = List.Repeat(List.Alternate(List.Skip(Table.ColumnNames(Source), 2),6,1,1), Table.RowCount(Source)),
    Transform = List.TransformMany(Table.ToRows(Source),
        each List.Transform(List.Split(List.Skip(_, 2), 7), List.Skip),
        (x,y)=> {x{0}} & {x{1}} & y      
 ),
    ListRowsToListColumns = Table.ToColumns(Table.FromRows(Transform)),
    ToTable = Table.FromColumns(List.FirstN(ListRowsToListColumns, 2) & {Professions} & List.Range(ListRowsToListColumns, 2), List.FirstN(ColNames, 2) & {"Professions"} & List.Range(ColNames, 3, 6)),
    ChangedType = Table.TransformColumnTypes(ToTable,{{"Month", type text}, {"Place", type text}, {"Professions", type text}, {"Girls<17", Int64.Type}, {"Boys <17", Int64.Type}, {"Women Adult", Int64.Type}, {"Men Adult", Int64.Type}, {"Elderly Women", Int64.Type}, {"Elderly Men", Int64.Type}})
in
    ChangedType

Task2 Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUQrISUxOVXAEsiJTi4GkERAbALEhEJtAaUO4rAGSLEilMZSGyMJEYTIgvqlSrA6mfU5All8+koHoGNU6AzRDEdYZQ51pArZKR8kMbJ1balIRDv8hG2eIxCfRNYiAiI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Place = _t, Engineer = _t, #"Girls<17" = _t, #"Boys <17" = _t, #"Women Adult" = _t, #"Men Adult" = _t, #"Elderly Women" = _t, #"Elderly Men" = _t, Doctor = _t, #"Girls<172" = _t, #"Boys <173" = _t, #"Women Adult4" = _t, #"Men Adult5" = _t, #"Elderly Women6" = _t, #"Elderly Men7" = _t, Teacher = _t, #"Girls<178" = _t, #"Boys <179" = _t, #"Women Adult10" = _t, #"Men Adult11" = _t, #"Elderly Women12" = _t, #"Elderly Men13" = _t]),
    ColNames = Table.ColumnNames(Source),
    ProfessionsSexAge = [ a = List.Alternate(List.Skip(ColNames,2),6,1,1), //Professions
    b = List.Alternate(List.Skip(ColNames,2),1,6), //Repeated columns
    c = List.Count(b) / List.Count(a), //Count for repeat
    d = List.Combine(List.Transform(a, each List.Repeat({_}, c))), //Final Professions List
    //e = Table.AddColumn(Table.FromList(b), "Sex", each if Text.Contains([Column1], "Women", Comparer.OrdinalIgnoreCase) or Text.Contains([Column1], "Girl", Comparer.OrdinalIgnoreCase) then "F" else "M"), //Add Sex
    //f = Table.AddColumn(e, "Age", each if List.Contains({"G", "B"}, Text.Start([Column1],1), Comparer.OrdinalIgnoreCase) then "0-17" else if Text.StartsWith([Column1], "E", Comparer.OrdinalIgnoreCase) then "60+" else "18-59"), //Add Age
    //g = Table.ToList(Table.CombineColumns(Table.RemoveColumns(f, {"Column1"}) ,{"Sex", "Age"},Combiner.CombineTextByDelimiter("||", QuoteStyle.None),"Merged")), //Sex and Age as merged list
    //h = List.Transform(List.Zip({ d, g }), each Text.Combine(_, "||")), //Final merged list: Profession || Sex || Age
    i = List.Repeat({"F","M"}, List.Count(b) / 2), //Sex list
    j = List.Repeat({"0-17", "0-17", "18-59", "18-59", "60+", "60+"},3), //Age list
    k = List.Transform(List.Zip({ d, i, j }), each Text.Combine(_, "||")) //Final merged list: Profession || Sex || Age (Same as step h, but faster)
  ][b],
    Transform = List.TransformMany(Table.ToRows(Source),
        each {{List.Alternate(List.Skip(_,2),1,6)}},
        (x,y)=> {x{0}} & {x{1}} & y
),
    ToTable = Table.FromRows(Transform),
    TransformColumn3 = Table.TransformColumns(ToTable, {{"Column3", each List.Transform(List.Zip({ ProfessionsSexAge, _ }), (x)=> Text.Combine(x, "||")), type list}}),
    ExpandedColumn3 = Table.ExpandListColumn(TransformColumn3, "Column3"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedColumn3, "Column3", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Column3.1", "Column3.2", "Column3.3", "Column3.4"}),
    RenamedColumns = Table.RenameColumns(SplitColumnByDelimiter,{{"Column1", "Month"}, {"Column2", "Place"}, {"Column3.1", "Profession"}, {"Column3.2", "Sex"}, {"Column3.3", "Age"}, {"Column3.4", "Number"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Month", type text}, {"Place", type text}, {"Profession", type text}, {"Sex", type text}, {"Age", type text}, {"Number", Int64.Type}})
in
    ChangedType

[enoch99]

@dufoq3 thank you very much. Being a beginer to M programming, I found this a bit easier to understand. 

[ronrsnfld · Accepted solution]

To create the first table:

Data

Month	Place	Engineer	Girls<17	Boys <17	Women Adult	Men Adult	Elderly Women	Elderly Men	Doctor	Girls<172	Boys <173	Women Adult4	Men Adult5	Elderly Women6	Elderly Men7	Teacher	Girls<178	Boys <179	Women Adult10	Men Adult11	Elderly Women12	Elderly Men13
January	Place A	Yes	2	0	1	4	1	1	Yes	0	0	1	2	3	2	Yes	1	2	2	3	1	5
January	Place B	No	0	0	0	0	0	0	Yes	0	0	0	2	3	1	Yes	1	3	4	4	5	6
February	Place A	Yes	0	0	2	1	0	0	No	0	0	0	0	0	0	Yes	0	0	0	0	0	1

let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Month/Place/Profession" = List.Alternate(Table.ColumnNames(Source),6,1,3),
    #"Base Attributes" = List.FirstN(List.RemoveFirstN(Table.ColumnNames(Source),3),6),

//Replace nulls with 0's to retain all rows when we unpivot
    #"Replace Null with 0" = Table.ReplaceValue(Source,null,0,
        Replacer.ReplaceValue,List.RemoveMatchingItems(Table.ColumnNames(Source),#"Month/Place/Profession")),
    unPivot = Table.UnpivotOtherColumns(#"Replace Null with 0", {"Month","Place"},"Attribute","Value"),

//Add column just for Professions
    #"Added Custom" = Table.AddColumn(unPivot, "Profession", each if [Value]="Yes" or [Value]="No" then [Attribute] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Profession"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "No" and [Value] <> "Yes")),

//Remove the trailing numbers added to column names to prevent duplicate column names in original Source table
    #"Attribute Translation" = List.Zip({
            #"Filtered Rows"[Attribute],
            List.Repeat(#"Base Attributes", Number.IntegerDivide(Table.RowCount(#"Filtered Rows"),6))
            }),
    #"Rename Attributes" = Table.TransformColumns(#"Filtered Rows",{
        {"Attribute", (x)=> List.Select(#"Attribute Translation", each _{0}=x){0}{1}}
        }),

//Group and then Pivot each subgroup
    #"Grouped Rows" = Table.Group(#"Rename Attributes", {"Month", "Place", "Profession"}, {
        {"Pivot", each Table.Pivot(_, [Attribute], "Attribute","Value")}}),
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Grouped Rows", "Pivot", #"Base Attributes"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Pivot",
        List.Zip({Table.ColumnNames(#"Expanded Pivot"), List.Repeat({type text},3) & List.Repeat({Int64.Type},6)}))
in
    #"Changed Type"

Results

Second Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Month/Place/Profession" = List.Alternate(Table.ColumnNames(Source),6,1,3),
    #"Base Attributes" = List.FirstN(List.RemoveFirstN(Table.ColumnNames(Source),3),6),
    #"Replace Null with 0" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,List.RemoveMatchingItems(Table.ColumnNames(Source),#"Month/Place/Profession")),
    unPivot = Table.UnpivotOtherColumns(#"Replace Null with 0", {"Month","Place"},"Attribute","Value"),
    #"Added Custom" = Table.AddColumn(unPivot, "Profession", each if [Value]="Yes" or [Value]="No" then [Attribute] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Profession"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "No" and [Value] <> "Yes")),
    #"Add Sex" = Table.AddColumn(#"Filtered Rows","Sex", each if
        Text.Contains([Attribute],"Girl") or Text.Contains([Attribute],"Women") then "F" else "M",type text),
    #"Add Age" = Table.AddColumn(#"Add Sex", "Age", each 
        if Text.Contains([Attribute], "<") then "0-17"
        else if Text.Contains([Attribute],"Elderly") then "60+"
        else "18-59", type text ),
    #"Removed Columns" = Table.RemoveColumns(#"Add Age",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Month", "Place", "Profession", "Sex", "Age"}, {{"Number", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

2nd table results, filtered to show only January / Place A / Engineer

[enoch99]

@ronrsnfld thank you very much for your quick response. This works perfectly. Sorry for not attaching the sample data. 

[dufoq3]

Hi, provide sample data as table so we can copy/paste. More info in link below.