[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Distance", type number}, {"Last Name", type text}, {"First Name", type text}, {"Level", type text}, {"Mail Street Number1", type text}, {"Mail Street Number2", type text}, {"Mail City", type text}, {"Mail State", type text}, {"Mail Zip Code", type text}, {"Building", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Mail Street Number1"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Distance", each Table.Column([Count],"Distance")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Distance", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom7" = Table.AddColumn(#"Extracted Values1", "Last Name", each Table.Column([Count],"Last Name")),
#"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"Last Name", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom2" = Table.AddColumn(#"Extracted Values7", "First Name", each Table.Column([Count],"First Name")),
#"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"First Name", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Level", each Table.Column([Count],"Level")),
#"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Level", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Mail Street Number1.1", each Table.Column([Count],"Mail Street Number1")),
#"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Mail Street Number1.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom5" = Table.AddColumn(#"Extracted Values4", "Mail Street Number2", each Table.Column([Count],"Mail Street Number2")),
#"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"Mail Street Number2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Building.1", each Table.Column([Count],"Building")),
#"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Building.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values6",{"Mail Street Number1", "Count"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3", "ID.4", "ID.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", Int64.Type}, {"ID.2", Int64.Type}, {"ID.3", Int64.Type}, {"ID.4", Int64.Type}, {"ID.5", Int64.Type}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Distance", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Distance.1", "Distance.2", "Distance.3", "Distance.4", "Distance.5"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Distance.1", type number}, {"Distance.2", type number}, {"Distance.3", type number}, {"Distance.4", type number}, {"Distance.5", type number}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Last Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Last Name.1", "Last Name.2", "Last Name.3", "Last Name.4", "Last Name.5"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Last Name.1", type text}, {"Last Name.2", type text}, {"Last Name.3", type text}, {"Last Name.4", type text}, {"Last Name.5", type text}}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "First Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"First Name.1", "First Name.2", "First Name.3", "First Name.4", "First Name.5"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"First Name.1", type text}, {"First Name.2", type text}, {"First Name.3", type text}, {"First Name.4", type text}, {"First Name.5", type text}}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4", "Level", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Level.1", "Level.2", "Level.3", "Level.4", "Level.5"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Level.1", type text}, {"Level.2", Int64.Type}, {"Level.3", Int64.Type}, {"Level.4", Int64.Type}, {"Level.5", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type5",{{"Mail Street Number1.1", "Mail Street Number1"}}),
#"Split Column by Delimiter5" = Table.SplitColumn(#"Renamed Columns", "Mail Street Number1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Mail Street Number1.1", "Mail Street Number1.2", "Mail Street Number1.3", "Mail Street Number1.4", "Mail Street Number1.5"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"Mail Street Number1.1", type text}, {"Mail Street Number1.2", type text}, {"Mail Street Number1.3", type text}, {"Mail Street Number1.4", type text}, {"Mail Street Number1.5", type text}}),
#"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type6", "Mail Street Number2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Mail Street Number2.1", "Mail Street Number2.2"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"Mail Street Number2.1", type text}, {"Mail Street Number2.2", type text}}),
#"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type7", "Building.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Building.1.1", "Building.1.2", "Building.1.3", "Building.1.4", "Building.1.5"}),
#"Changed Type8" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"Building.1.1", type text}, {"Building.1.2", type text}, {"Building.1.3", type text}, {"Building.1.4", type text}, {"Building.1.5", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type8"),
#"Changed Type9" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type9"),
#"Inserted Last Characters" = Table.AddColumn(#"Transposed Table", "Last Characters", each Text.End([Column1], 1), type text),
#"Added Index" = Table.AddIndexColumn(#"Inserted Last Characters", "Index", 1, 1),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Last Characters", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Last Characters", "Index"}),
#"Transposed Table1" = Table.Transpose(#"Removed Columns1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type10" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID.1", Int64.Type}, {"Distance.1", type number}, {"Last Name.1", type text}, {"First Name.1", type text}, {"Level.1", type text}, {"Mail Street Number1.1", type text}, {"Mail Street Number2.1", type text}, {"Building.1.1", type text}, {"ID.2", Int64.Type}, {"Distance.2", type number}, {"Last Name.2", type text}, {"First Name.2", type text}, {"Level.2", Int64.Type}, {"Mail Street Number1.2", type text}, {"Mail Street Number2.2", type any}, {"Building.1.2", type text}, {"ID.3", Int64.Type}, {"Distance.3", type number}, {"Last Name.3", type text}, {"First Name.3", type text}, {"Level.3", Int64.Type}, {"Mail Street Number1.3", type text}, {"Building.1.3", type text}, {"ID.4", Int64.Type}, {"Distance.4", type number}, {"Last Name.4", type text}, {"First Name.4", type text}, {"Level.4", Int64.Type}, {"Mail Street Number1.4", type text}, {"Building.1.4", type text}, {"ID.5", Int64.Type}, {"Distance.5", type number}, {"Last Name.5", type text}, {"First Name.5", type text}, {"Level.5", Int64.Type}, {"Mail Street Number1.5", type text}, {"Building.1.5", type text}})
in
#"Changed Type10"[/SIZE]
