Forum Discussion

Jpalaci1's avatar
Jpalaci1
Brass Contributor
Jan 13, 2021

Excel Formulas to Power Query formulas Help

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. Ho...
BI & Data Analysis
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 13, 2021

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.

Resources