Brian Julius Solution to Tanya McIlravy PQ Challenge

gistfile1.txt

let
    Source = DataRaw,
    AddUnpivotFlag = Table.AddColumn(Source, "UnpivotFlag", each if Text.StartsWith( [Action], "EXT") then Text.From( [DATE1A] ) & "*" & Text.From( [DATE2A] ) else null),
    SplitFlagToRows = Table.ExpandListColumn(Table.TransformColumns(AddUnpivotFlag, {{"UnpivotFlag", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "UnpivotFlag"),
    Group = Table.Group(SplitFlagToRows, {"ID"}, {{"All", each _, type table [ID=nullable text, PRICE=nullable number, QTY=nullable number, DATE1=nullable date, DATE2=nullable date, Action=nullable text, DATE1A=nullable date, DATE2A=nullable date, UnpivotFlag=nullable text]}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group, "Idx", each Table.AddIndexColumn( [All], "Index", 1, 1 )), "All"),
    AddActionz = Table.AddColumn( AddIndex, "Actionz", each Table.AddColumn( [Idx], "ACTIONz", each if [Index] = 1 then [Action] else null), Text.Type),
    AddDate1z = Table.AddColumn(AddActionz, "Date1z", each Table.AddColumn( [Actionz], "DATE1z", each if [Index] = 1 then [DATE1] else [DATE1A]), Date.Type),
    AddDate2z = Table.AddColumn(AddDate1z, "Date2z", each Table.AddColumn( [Date1z], "DATE2z", each if [Index] = 1 then [DATE2] else [DATE2A]), Date.Type),
    AddDate1Az = Table.AddColumn(AddDate2z, "Date1Az", each Table.AddColumn( [Date2z], "DATE1Az", each if [Index] = 1 then [DATE1A] else null), Date.Type),
    AddDate2Az =Table.ExpandTableColumn( Table.SelectColumns( Table.AddColumn(AddDate1Az, "Date2Az", each Table.AddColumn( [Date1Az], "DATE2Az", each if [Index] = 1 then [DATE2A] else null), Date.Type), {"ID", "Date2Az"}), "Date2Az", { "PRICE", "QTY", "DATE1z", "DATE2z", "ACTIONz", "DATE1Az", "DATE2Az"}),
    Rename = Table.RenameColumns(AddDate2Az,{{"DATE1z", "DATE1"}, {"DATE2z", "DATE2"}, {"DATE1Az", "DATE1A"}, {"DATE2Az", "DATE2A"}, {"ACTIONz","Action"}})
in
    Rename