Chandeep Problem – Brian Julius Solution

gistfile1.txt

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClGK1YlWCtLx1QkGs9x0QOxwnVAwLxzIC9Fxg7J9wXQwWB6iD6gLJBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Days = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Days", "Days - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Days - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Days - Copy"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Days - Copy", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Days - Copy", "Key"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Key"}, #"Days Reference", {"Letter"}, "Days Reference", JoinKind.LeftOuter),
    #"Expanded Days Reference" = Table.ExpandTableColumn(#"Merged Queries", "Days Reference", {"Day", "DaySort"}, {"Day", "DaySort"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Days Reference", "Value", each "TRUE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Key"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"DaySort", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"DaySort"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Day]), "Day", "Value"),
    #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows1",{"Index"})
in
    #"Removed Columns2"