A | B | C | D | E | F | G | H | I | J | K | L | M | |
1 | Custom.Record No | NAME | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 |
2 | 1 | BALA | PERSONALITY | GOOD | HOBBIES | PLAYING CRICKET | COUNTRY | INDIAN | GENDER | MALE | EDUCATION | | |
3 | 2 | VIJAY | TYPE OF PERSON | GOOD | BEHAVIOUR | GENDER | COUNTRY | MALE | | | | | |
4 | 3 | VINOTH | TYPE OF CHARACTER | PLAYBOY | GENDER | MALE | COUNTRY | | | | | | |
5 | 4 | VINO | PERSONALITY | GOOD | HOBBIES | PLAYING CRICKET | COUNTRY | INDIAN | GENDER | MALE | EDUCATION | JOB TYPE | PERMANENT |
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Record No", Int64.Type}, {"NAME", type text}, {"Characteristic", type text}, {"Value", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record No", "NAME"}, "Attribute", "Value.1"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"NAME"}, {{"Data", each _, type table [Record No=nullable number, NAME=nullable text, Value.1=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Record No", "Value.1", "Index"}, {"Custom.Record No", "Custom.Value.1", "Custom.Index"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom.Record No", "NAME", "Custom.Value.1", "Custom.Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Value.1")
in
#"Pivoted Column"