SOLVED

Power Query Multiple IF Conditions in Custom Column

%3CLINGO-SUB%20id%3D%22lingo-sub-3031460%22%20slang%3D%22en-US%22%3EPower%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031460%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20calculated%20in%20Excel%20that%20I%20am%20now%20looking%20to%20calculate%20in%20Power%20Query.%20I%20know%20I%20can%20only%20use%20column%20reference%20while%20writing%20IF%20statements%20in%20PQ.%20Can%20you%20please%20let%20me%20know%20what%20the%20Custom%20Column%20would%20be%20based%20on%20the%20below%20Excel%20calculation%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(M5%3DN5%2CM5%2CIF(AND(M5%3D%22BLANK1%22%2CN5%3D%22BLANK2%22)%2C%22Investigate%22%2CIF(AND(M5%26lt%3B%26gt%3B%22BLANK1%22%2CN5%3D%22BLANK2%22)%2CM5%2CIF(AND(M5%26lt%3B%26gt%3B%22BLANK1%22%2CN5%26lt%3B%26gt%3B%22BLANK2%22%2CM5%26lt%3B%26gt%3BN5)%2C%22300-Corporate%22%2CIF(AND(M5%3D%22BLANK1%22%2CN5%26lt%3B%26gt%3B%22BLANK2%22)%2CN5%2C%22ERROR%22)))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3031460%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3033256%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3033256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241002%22%20target%3D%22_blank%22%3E%40Alicia_Bucci%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConvert%20logic%20(if%20I%20understood%20it%20correctly)%20to%20Power%20Query%20that's%20with%20some%20coding.%3C%2FP%3E%0A%3CP%3EFirst%20way%20with%20minimum%20one%3C%2FP%3E%0A%3CP%3E-%20query%20the%20table%20and%20add%20Index%2C%20nothing%20more%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332393i57A93C120E7C88E5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20reference%20this%20one%2C%20remove%20all%20columns%20but%20Index%20and%20all%20AST..%20Select%20Index%20and%20Unpivot%20Other%20columns%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20517px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332394i3AA7785393205C9B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20another%20column%20replacing%20all%20values%20where%20Text.StartsWith%20%22BLANK%22%20replace%20on%20null%20and%20remove%20Value%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20515px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332395i72F3B6994503DF47%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20Group%20By%20column%20Index%20without%20aggregation%2C%20manually%20add%20selection%20only%20of%20Custom%20column%20in%20result%20and%20keeping%20distinct%20values%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20673px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332396i2227204C83963BA2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20another%20column%20removing%20rows%20with%20null%20from%20above%2C%20remove%20Data%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20561px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332397i4EB9A4963952D1A5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20column%20with%20number%20of%20rows%20in%20each%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20537px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332398iA54A36782B13F1A7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20one%20more%20columns%20with%20text%20in%20first%20row%20of%20each%20table%20and%20remove%20column%20with%20tables%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20657px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332399i3246F55CF2137964%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20column%20with%20conditional%20result%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20655px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332400i4F5304205B27A218%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20remove%20other%20but%20Index%20and%20Result%20columns%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20merge%20first%20table%20with%20above%20one%20on%20Index%20into%20new%20query%2C%20expand%20Result.%3C%2FP%3E%0A%3CP%3EIf%20you%20add%20more%20columns%20the%20only%20you%20need%20is%20to%20change%20columns%20selected%20at%20the%20beginning%20of%20second%20query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%20do%20everything%20with%20lists%2C%20more%20coding%2C%20perhaps%20bit%20more%20flexible%20and%20less%20steps.%20Not%20sure%20that's%20better%2C%20Power%20Query%20is%20optimized%20for%20tables%2C%20not%20lists.%20And%20here%20is%20sample%20how%20to%20automate%20columns%20selection%20assuming%20some%20logic%20in%20their%20names.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22data%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(%0A%20%20%20%20%20%20%20%20Source%2C%20%5BPromoteAllScalars%3Dtrue%5D)%2C%0A%20%20%20%20prevStep%20%3D%20%23%22Promoted%20Headers%22%2C%0A%0A%20%20%20%20GetValues%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20prevStep%2C%0A%20%20%20%20%20%20%20%20%22values%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20Record.ToList(%0A%20%20%20%20%20%20%20%20%20%20%20%20Record.SelectFields(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20_%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.Select(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table.ColumnNames(%20prevStep%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%20Text.StartsWith(_%2C%20%22AST%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20))%2C%0A%20%20%20%20CleanLists%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20GetValues%2C%0A%20%20%20%20%20%20%20%20%22notBlanks%22%2C%0A%20%20%20%20%20%20%20%20each%20List.Distinct(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.RemoveNulls(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5Bvalues%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20Text.StartsWith(%20_%2C%20%22BLANK%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20_%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%20)%2C%0A%20%20%20%20RemoveValues%20%3D%20Table.RemoveColumns(CleanLists%2C%7B%22values%22%7D)%2C%0A%20%20%20%20ApplyCondition%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20RemoveValues%2C%0A%20%20%20%20%20%20%20%20%22Result%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20List.IsEmpty(%20%5BnotBlanks%5D%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20%22Investigate%22%0A%20%20%20%20%20%20%20%20%20%20%20%20else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20List.Count(%20%5BnotBlanks%5D%20)%20%3D%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20List.Single(%20%5BnotBlanks%5D%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20%22House%22%0A%20%20%20%20)%2C%0A%20%20%20%20RemoveLists%20%3D%20Table.RemoveColumns(%0A%20%20%20%20%20%20%20%20ApplyCondition%2C%0A%20%20%20%20%20%20%20%20%7B%22notBlanks%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20RemoveLists%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPerhaps%20other%20variant%20exist%2C%20but%20in%20any%20case%20for%20flexible%20number%20of%20columns%20on%20which%20make%20the%20selection%20that%20will%20be%20bit%20more%20complex%20than%20nested%20if%20...%20then%20...%20else%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3033128%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3033128%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20Excel%20IF%20statement%2C%20which%20gets%20more%20complex%20when%20adding%20AST3.%20My%20issue%20that%20I%20am%20running%20into%20is%20when%20I%20need%20to%20add%20AST4%2C%20AST5%2C%20and%20possibly%20AST6.%20Is%20there%20a%20better%20way%20to%20calculate%20this%20formula%20with%20adding%20the%20additional%20AST%20from%201-6%3F%20Would%20this%20be%20easier%20to%20formulate%20in%20Power%20BI%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(AND(A2%3D%22BLANK1%22%2CB2%3D%22BLANK2%22%2CC2%3D%22BLANK3%22)%2C%22Investigate%22%2CIF(AND(A2%3DB2%2CA2%3DC2)%2CA2%2CIF(AND(A2%26lt%3B%26gt%3B%22BLANK1%22%2CB2%3D%22BLANK2%22%2CC2%3D%22BLANK3%22)%2CA2%2CIF(AND(A2%3DC2%2CB2%3D%22BLANK2%22)%2CA2%2CIF(AND(A2%26lt%3B%26gt%3BC2%2CA2%26lt%3B%26gt%3B%22BLANK1%22%2CC2%26lt%3B%26gt%3B%22BLANK3%22%2CB2%3D%22BLANK2%22)%2C%22House%22%2CIF(AND(A2%26lt%3B%26gt%3B%22BLANK1%22%2CB2%26lt%3B%26gt%3B%22BLANK2%22%2CA2%26lt%3B%26gt%3BB2%2CC2%3D%22BLANK3%22)%2C%22House%22%2CIF(AND(A2%3D%22BLANK1%22%2CB2%26lt%3B%26gt%3B%22BLANK2%22%2CC2%3D%22BLANK3%22)%2CB2%2CIF(AND(A2%3D%22BLANK1%22%2CB2%26lt%3B%26gt%3B%22BLANK2%22%2CB2%26lt%3B%26gt%3BC2)%2C%22House%22%2CIF(AND(A2%3D%22BLANK1%22%2CB2%3D%22BLANK2%22%2CC2%26lt%3B%26gt%3B%22BLANK3%22)%2CC2%2CIF(AND(A2%26lt%3B%26gt%3B%22BLANK1%22%2CB2%26lt%3B%26gt%3B%22BLANK2%22%2CC2%26lt%3B%26gt%3B%22BLANK3%22%2CA2%26lt%3B%26gt%3BB2%2CB2%26lt%3B%26gt%3BC2)%2C%22House%22%2CIF(AND(A2%3D%22BLANK1%22%2CB2%3DC2)%2CB2%2C%22ERROR%22)))))))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20illustration%20of%20how%20this%20is%20laid%20out%20in%20Excel.%20I%20included%20a%20copy%20of%20the%20file%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22725%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22107%22%3EAST1%3C%2FTD%3E%3CTD%20width%3D%22107%22%3EAST2%3C%2FTD%3E%3CTD%20width%3D%22107%22%3EAST3%3C%2FTD%3E%3CTD%20width%3D%22131%22%3EExpected%20Result%3C%2FTD%3E%3CTD%20width%3D%22148%22%3ELine%20Item%20Formula%3C%2FTD%3E%3CTD%20width%3D%22125%22%3EComp%20Territory%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBLANK1%3C%2FTD%3E%3CTD%3EBLANK2%3C%2FTD%3E%3CTD%3EBLANK3%3C%2FTD%3E%3CTD%3EInvestigate%3C%2FTD%3E%3CTD%3EInvestigate%3C%2FTD%3E%3CTD%3EInvestigate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3EBLANK2%3C%2FTD%3E%3CTD%3EBLANK3%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3EBLANK2%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3EBLANK2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3EBLANK3%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBLANK1%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3EBLANK3%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBLANK1%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBLANK1%3C%2FTD%3E%3CTD%3EBLANK2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET3%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3CTD%3EHouse%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBLANK1%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3ET2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031916%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241002%22%20target%3D%22_blank%22%3E%40Alicia_Bucci%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20only%20tried%20to%20illustrate%20what%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bsuggested%20and%20didn't%20test%20the%20formula.%20As%20for%20O5%20-%20question%20is%20bit%20abstract%2C%20not%20clear%20in%20which%20part%20of%20formula%20you'd%20like%20to%20add%20it%20and%20what%20formula%20shall%20do%20with%20it.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20illustrate%20the%20logic%20if%20not%20with%20draft%20formula%2C%20when%20by%20diagram%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20469px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F332010i553CC8239C93A7BC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E(again%20didn't%20test)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031704%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20help!%20It%20is%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20wanted%20to%20add%20%22O5%22%20to%20this%20formula%2C%20is%20that%20doable%20or%20will%20that%20create%20an%20issue%20with%20the%20formula%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031594%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241002%22%20target%3D%22_blank%22%3E%40Alicia_Bucci%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20simplify%20initial%20formula%20first%2C%20perhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20M5%3DN5%2C%0A%20%20%20%20%20M5%2C%0A%20%20%20%20%20IF(%20M5%3D%22BLANK1%22%2C%0A%20%20%20%20%20%20%20%20%20IF(%20N5%3D%22BLANK2%22%2C%22Investigate%22%2C%20N5)%2C%0A%20%20%20%20%20%20%20%20%20IF(%20N5%3D%22BLANK2%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20M5%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20IF(%20M5%20%26lt%3B%26gt%3B%20N5%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22300-Corporate%22%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22ERROR%22%20)%20)%20)%20)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewith%20that%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3Dif%20%5BM%5D%20%3D%20%5BN%5D%0A%20then%20%5BM%5D%0A%20else%0A%20%20%20%20if%20%5BM%5D%20%3D%20%22BLANK1%22%0A%20%20%20%20then%0A%20%20%20%20%20%20%20%20if%20%5BN%5D%20%3D%20%22BLANK2%22%20then%20%22Investigate%22%20else%20%5BN%5D%0A%20%20%20%20else%0A%20%20%20%20%20%20%20%20if%20%5BN%5D%20%3D%22BLANK2%22%0A%20%20%20%20%20%20%20%20then%20%5BM%5D%0A%20%20%20%20%20%20%20%20else%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20%5BM%5D%20%26lt%3B%26gt%3B%20%5BN%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20%22300-Corporate%22%20%0A%20%20%20%20%20%20%20%20%20%20%20%20else%20%20%22ERROR%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031576%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241002%22%20target%3D%22_blank%22%3E%40Alicia_Bucci%3C%2FA%3E%26nbsp%3BThanks%2C%20but%20can't%20work%20on%20this%20file.%20It%20links%20to%20a%20file%20on%20your%20local%20system.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031509%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20struggling%20a%20bit%20with%20the%20difference%20from%20Excel%20IF%20function%20to%20PQ.%20Attached%20is%20a%20sample%20file%20I%20am%20working%20on.%20I%20was%20originally%20trying%20to%20work%20with%203%20columns%20to%20create%20an%20IF%20formula%20but%20could%20not%20get%20it%20to%20work%20the%20way%20I%20needed%20it%20to%2C%20so%20I%20am%20now%20going%20to%20work%20with%202%20columns%2C%20which%20is%20the%20original%20Excel%20formula%20I%20posted.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031504%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Multiple%20IF%20Conditions%20in%20Custom%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031504%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241002%22%20target%3D%22_blank%22%3E%40Alicia_Bucci%3C%2FA%3E%26nbsp%3BIn%20PQ%20the%20manner%20of%20writing%20IF%20formulae%20is%20quite%20different%20from%20Excel's%20IF%20function.%20It%20would%20look%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20%3CSTRONG%3Etry%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3Eif%3C%2FSTRONG%3E%20something%20%3CSTRONG%3Ethen%3C%2FSTRONG%3E%20this%20%3CSTRONG%3Eelse%3C%2FSTRONG%3E%20that%3C%2FP%3E%3CP%3E%3CSTRONG%3Eotherwise%3C%2FSTRONG%3E%20%22error%20message%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3Ewhere%20the%20%3CSTRONG%3Ebolded%3C%2FSTRONG%3E%20words%20are%20essential%20key%20words%20in%20the%20M-language.%20The%20non%20bolded%20words%20can%20be%20replaced%20by%20expressions%2C%20for%20example%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%5BColumn1%5D%20%26gt%3B%3D%2010%3C%2FEM%3E%3C%2FFONT%3E%2C%20or%20just%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CEM%3E%5BColumn2%5D%3C%2FEM%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20you%20can%20nest%20these%20if%20%2F%20then%20%2F%20else%20statements.%20How%20exactly%20is%20difficult%20to%20say%20without%20seeing%20your%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good morning, 

 

I have a formula calculated in Excel that I am now looking to calculate in Power Query. I know I can only use column reference while writing IF statements in PQ. Can you please let me know what the Custom Column would be based on the below Excel calculation?

 

=IF(M5=N5,M5,IF(AND(M5="BLANK1",N5="BLANK2"),"Investigate",IF(AND(M5<>"BLANK1",N5="BLANK2"),M5,IF(AND(M5<>"BLANK1",N5<>"BLANK2",M5<>N5),"300-Corporate",IF(AND(M5="BLANK1",N5<>"BLANK2"),N5,"ERROR")))))

 

Thank you in advance!

8 Replies

@Alicia_Bucci In PQ the manner of writing IF formulae is quite different from Excel's IF function. It would look something like this:

 

= try

if something then this else that

otherwise "error message"

 

where the bolded words are essential key words in the M-language. The non bolded words can be replaced by expressions, for example [Column1] >= 10, or just [Column2]

 

and you can nest these if / then / else statements. How exactly is difficult to say without seeing your file.

@Riny_van_Eekelen Thank you for the reply. 

 

I am struggling a bit with the difference from Excel IF function to PQ. Attached is a sample file I am working on. I was originally trying to work with 3 columns to create an IF formula but could not get it to work the way I needed it to, so I am now going to work with 2 columns, which is the original Excel formula I posted. 

@Alicia_Bucci Thanks, but can't work on this file. It links to a file on your local system.

best response confirmed by Alicia_Bucci (New Contributor)
Solution

@Alicia_Bucci 

I'd simplify initial formula first, perhaps

=IF( M5=N5,
     M5,
     IF( M5="BLANK1",
         IF( N5="BLANK2","Investigate", N5),
         IF( N5="BLANK2",
             M5,
             IF( M5 <> N5,
                 "300-Corporate", 
                 "ERROR" ) ) ) )

with that

=if [M] = [N]
 then [M]
 else
    if [M] = "BLANK1"
    then
        if [N] = "BLANK2" then "Investigate" else [N]
    else
        if [N] ="BLANK2"
        then [M]
        else
            if [M] <> [N]
            then "300-Corporate" 
            else  "ERROR"

 

@Sergei Baklan Thank you for your help! It is greatly appreciated. 

 

If I wanted to add "O5" to this formula, is that doable or will that create an issue with the formula? 

@Alicia_Bucci 

I only tried to illustrate what @Riny_van_Eekelen suggested and didn't test the formula. As for O5 - question is bit abstract, not clear in which part of formula you'd like to add it and what formula shall do with it. 

Perhaps you may illustrate the logic if not with draft formula, when by diagram like

image.png

(again didn't test)

@Sergei Baklan 

 

Here is the Excel IF statement, which gets more complex when adding AST3. My issue that I am running into is when I need to add AST4, AST5, and possibly AST6. Is there a better way to calculate this formula with adding the additional AST from 1-6? Would this be easier to formulate in Power BI?

 

IF(AND(A2="BLANK1",B2="BLANK2",C2="BLANK3"),"Investigate",IF(AND(A2=B2,A2=C2),A2,IF(AND(A2<>"BLANK1",B2="BLANK2",C2="BLANK3"),A2,IF(AND(A2=C2,B2="BLANK2"),A2,IF(AND(A2<>C2,A2<>"BLANK1",C2<>"BLANK3",B2="BLANK2"),"House",IF(AND(A2<>"BLANK1",B2<>"BLANK2",A2<>B2,C2="BLANK3"),"House",IF(AND(A2="BLANK1",B2<>"BLANK2",C2="BLANK3"),B2,IF(AND(A2="BLANK1",B2<>"BLANK2",B2<>C2),"House",IF(AND(A2="BLANK1",B2="BLANK2",C2<>"BLANK3"),C2,IF(AND(A2<>"BLANK1",B2<>"BLANK2",C2<>"BLANK3",A2<>B2,B2<>C2),"House",IF(AND(A2="BLANK1",B2=C2),B2,"ERROR")))))))))))

 

Here is the illustration of how this is laid out in Excel. I included a copy of the file as well. 

 

AST1AST2AST3Expected ResultLine Item FormulaComp Territory
BLANK1BLANK2BLANK3InvestigateInvestigateInvestigate
T1T1T1T1T1T1
T1BLANK2BLANK3T1T1T1
T1BLANK2T1T1T1T1
T1BLANK2T2HouseHouseHouse
T1T2BLANK3HouseHouseHouse
BLANK1T2BLANK3T2T2T2
BLANK1T2T1HouseHouseHouse
BLANK1BLANK2T2T2T2T2
T1T2T3HouseHouseHouse
BLANK1T2T2T2T2T2

@Alicia_Bucci 

Convert logic (if I understood it correctly) to Power Query that's with some coding.

First way with minimum one

- query the table and add Index, nothing more

image.png

- reference this one, remove all columns but Index and all AST.. Select Index and Unpivot Other columns

image.png

- add another column replacing all values where Text.StartsWith "BLANK" replace on null and remove Value column

image.png

- Group By column Index without aggregation, manually add selection only of Custom column in result and keeping distinct values

image.png

- add another column removing rows with null from above, remove Data column

image.png

- add column with number of rows in each table

image.png

- add one more columns with text in first row of each table and remove column with tables

image.png

- add column with conditional result 

image.png

and remove other but Index and Result columns

 

Now merge first table with above one on Index into new query, expand Result.

If you add more columns the only you need is to change columns selected at the beginning of second query.

 

Another variant is do everything with lists, more coding, perhaps bit more flexible and less steps. Not sure that's better, Power Query is optimized for tables, not lists. And here is sample how to automate columns selection assuming some logic in their names.

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(
        Source, [PromoteAllScalars=true]),
    prevStep = #"Promoted Headers",

    GetValues = Table.AddColumn(
        prevStep,
        "values",
        each
            Record.ToList(
            Record.SelectFields(
                _,
                List.Select(
                    Table.ColumnNames( prevStep ),
                    each Text.StartsWith(_, "AST")
                )
            )
    )),
    CleanLists = Table.AddColumn(
        GetValues,
        "notBlanks",
        each List.Distinct(
                List.RemoveNulls(
                    List.Transform(
                        [values],
                        each
                            if Text.StartsWith( _, "BLANK")
                            then null
                            else _ )
                )
        ) ),
    RemoveValues = Table.RemoveColumns(CleanLists,{"values"}),
    ApplyCondition = Table.AddColumn(
        RemoveValues,
        "Result",
        each
            if List.IsEmpty( [notBlanks] )
            then "Investigate"
            else
                if List.Count( [notBlanks] ) = 1
                then List.Single( [notBlanks] )
                else "House"
    ),
    RemoveLists = Table.RemoveColumns(
        ApplyCondition,
        {"notBlanks"}
    )
in
    RemoveLists

Perhaps other variant exist, but in any case for flexible number of columns on which make the selection that will be bit more complex than nested if ... then ... else