Excel Formulas to Power Query formulas Help

Question

I’m having troubles with the formulas I created in Excel then going to Power Query for my first time. After creating and modeling in Excel, I'm not sure how to go about making this possible in PQ. How do I go from an Excel formula to an M code equivalent?

Use IF(OR( to determine type

=IF(OR(A2=999999,A2=444444),"Type 1","Type 2")

Use the result of the first formula in an if and if false then VLOOKUP IF(VLOOKUP(. How Would I go from an IF with a false being an function (in this case VLOOKUP)?

=IFERROR(IF(D2="Type 1","Other",VLOOKUP(B2,Table2,2,0)),"-")

Take a short date MM/DD/YYYY and get week number starting on Monday

=WEEKNUM(C2,21)

Mapping calendar days to conform to a 4-4-5 accounting calendar

=IFERROR(IFS(AND(WEEKNUM(C2)>=1,WEEKNUM(C2)<=4),"1",AND(WEEKNUM(C2)>=5,WEEKNUM(C2)<=8),"2",AND(WEEKNUM(C2)>=9,WEEKNUM(C2)<=13),"3",AND(WEEKNUM(C2)>=14,WEEKNUM(C2)<=17),"4",AND(WEEKNUM(C2)>=18,WEEKNUM(C2)<=21),"5",AND(WEEKNUM(C2)>=22,WEEKNUM(C2)<=26),"6",AND(WEEKNUM(C2)>=27,WEEKNUM(C2)<=30),"7",AND(WEEKNUM(C2)>=31,WEEKNUM(C2)<=34),"8",AND(WEEKNUM(C2)>=35,WEEKNUM(C2)<=39),"9",AND(WEEKNUM(C2)>=40,WEEKNUM(C2)<=43),"10",AND(WEEKNUM(C2)>=44,WEEKNUM(C2)<=47),"11",AND(WEEKNUM(C2)>=48,WEEKNUM(C2)<=53),"12"),"-")

Know If data is from this year, last year, 2 years ago, 3 year ago, or older

=IFERROR(IFS(AND(ABS($C2-TODAY())>=1,ABS($C2-TODAY())<=365),12,AND(ABS($C2-TODAY())>=366,ABS($C2-TODAY())<=730),24,AND(ABS($C2-TODAY())>=731,ABS($C2-TODAY())<=1095),36,AND(ABS($C2-TODAY())>=1096,ABS($C2-TODAY())<=1491),48,AND(ABS($C2-TODAY())>=1492,ABS($C2-TODAY())<=3500),">48"),"-")

Thank you!

sergeibaklan · Answer

Jpalaci1&nbsp;
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.

jpalaci1 · Answer

SergeiBaklan&nbsp;Thank you so much!&nbsp;I wasn't at first mapping/creating everything in Excel first rather taking what I have in Excel then turning that in Power Query since I already finished and know this is what I want to get to.

Forum Discussion

Excel Formulas to Power Query formulas Help

Resources