Excel BI Power Query Challenge 127 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.Workbook(File.Contents("C:\Users\brjul\Downloads\PQ_Challenge_127.xlsx"), null, true),
    Expand = Table.PromoteHeaders( Table.SelectColumns( Table.ExpandTableColumn(Source, "Data", {"Column1", "Column2", "Column4"}, {"Column1", "Column2", "Column4"}), {"Column1", "Column2", "Column4"})),
    T1 = Table.SelectColumns( Expand, {"Name", "Date of Birth"}),
    T1Final = Table.SelectRows(T1, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    T2 = Table.SelectColumns(Expand, "Zodiac Signs"),
    Split = Table.SplitColumn(T2, "Zodiac Signs", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Zodiac Signs", "Start"}),
    TrimParens = Table.TransformColumns(Split, {{"Start", each Text.Trim(_, {"(", ")"} ),type text}}),
    Split2 = Table.SplitColumn(TrimParens, "Start", Splitter.SplitTextByEachDelimiter({" – "}, QuoteStyle.Csv, false), {"Start", "End"}),
    ReType = Table.TransformColumnTypes(Split2,{{"Zodiac Signs", type text}, {"Start", type date}, {"End", type date}}),
    T2Final = Table.ReplaceValue(ReType,#date(2023, 1, 19),#date(2023, 12, 31),Replacer.ReplaceValue,{"End"}),
    T3 = T2Final,
    Filter = Table.SelectRows(T3, each ([Zodiac Signs] = "Capricorn")),
    Replace1 = Table.ReplaceValue(Filter,#date(2023, 12, 22),#date(2023, 1, 1),Replacer.ReplaceValue,{"Start"}),
    T3Final = Table.ReplaceValue(Replace1,#date(2023, 12, 31),#date(2023, 1, 19),Replacer.ReplaceValue,{"End"}),
    Signs = Table.Combine( {T2Final, T3Final}),
    DateList = Table.AddColumn(Signs, "Date", each [
        a1 = Number.From([Start]),
        b1 = Number.From([End] ),
        c = {a1..b1},
        d = List.Transform(c, each Date.From(_))
    ][d]),
    RemovCols = Table.RemoveColumns(DateList,{"Start", "End"}),
    SignsFinal = Table.ExpandListColumn(RemovCols, "Date"),
    Join = Table.Join(T1Final, "Date of Birth", SignsFinal, "Date", JoinKind.LeftOuter),
    Clean = Table.RemoveColumns(Join,{"Date"})
in
    Clean