Excel BI Excel Power Query Challenge 155 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextTable = Table.RenameColumns( Table.SelectColumns(Source, "Column9"), {"Column9", "Text"}),
    #"Filtered Rows" = Table.SelectRows(TextTable, each ([Text] <> null)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows", "Text", "Text2"),
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Text2", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text2"),
    Final1 = Table.Buffer( Table.TransformColumns(#"Split Column by Position",{{"Text2", Text.Upper, type text}})),
    #"Added Index" = Table.AddIndexColumn(Final1, "Index", 1, 1, Int64.Type),
    TempTable = Table.RemoveFirstN( Table.RemoveColumns( Source, {"Column8", "Column9"}), 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TempTable, {"Column1"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("Column", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute.1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Column1", type text}, {"Attribute.2", type text}}, "en-US"),{"Column1", "Attribute.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Numeric"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Value", type text}, {"Numeric", Int64.Type}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Changed Type1", "Number", each [Numeric] - 1, type number),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Subtraction",{"Numeric"}),
    Final2 = Table.TransformColumnTypes(#"Removed Columns1",{{"Number", type text}}),
    Join = Table.Join( #"Added Index", "Text2", Final2, "Value", JoinKind.LeftOuter),
    #"Sorted Rows" = Table.Sort(Join,{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Code", each if [Number] = null then [Text2]  else [Number]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Text2", "Index", "Value", "Number"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Text"}, {{"All", each [Code]}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"All", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Values",{{"All", "Answer"}})
in
    #"Renamed Columns"