Excel Formulas to Power Query formulas Help

Jpalaci1 

Power Query has another logic, it's note necessary to map Excel formulas. First part could be as

let
    Source = Table1,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Account Number", "Sales Area #", "Date"}),
    #"Merged Queries" = Table.NestedJoin(
        #"Removed Other Columns", {"Sales Area #"},
        Table2, {"#"}, "Table2",
        JoinKind.LeftOuter
    ),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Area"}, {"Area"}),
    #"Replaced Value" = Table.ReplaceValue(
        #"Expanded Table2",
        each [Area],
        each
            if [Account Number]=999999 or [Account Number]=444444
            then "Other"
            else [Area],
    Replacer.ReplaceValue,{"Area"}
),
    #"Inserted Week of Year" = Table.AddColumn(
        #"Replaced Value",
        "Week of Year",
        each Date.WeekOfYear([Date]), Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Week of Year",{{"Date", type date}})
in
    #"Changed Type"

practically everything from UI. Not sure right now about other calendars, perhaps mapping tables could help.