Forum Discussion

MMG_F1RST's avatar
MMG_F1RST
New Member
3 years ago

Powequery Formula using If inside other ifs

Hi,

I have a foruma in excel that im converting into Powerbi (Powerquery) but im having trouble to recreaty if inside other ifs.

IF($AS6<DATE(YEAR(AZ$5);MONTH(AZ$5);1);
 IF($AU6="";NETWORKDAYS(DATE(YEAR(AZ$5);MONTH(AZ$5);1);DATE(YEAR(AZ$5);MONTH(AZ$5)+1;1)-1;Feriado[Nome])*8;
 IF($AU6<DATE(YEAR(AZ$5);MONTH(AZ$5);1);"0";   IF($AU6>=DATE(YEAR(AZ$5);MONTH(AZ$5)+1;1)-1;NETWORKDAYS(DATE(YEAR(AZ$5);MONTH(AZ$5);1);DATE(YEAR(AZ$5);MONTH(AZ$5)+1;1)-1;Feriado[Nome])*8;NETWORKDAYS(DATE(YEAR(AZ$5);MONTH(AZ$5);1);$AU6;Feriado[Nome])*8)));........)

Is there a way to recreate this kind of excel forumla in Powerquery mode inside Powerbi?

Thx

Need Help

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    3 years ago

    Yes, and it is easier. In Excel it is IF(Condition, True, False) and you nest other statemetns in the FALSE section.

     

    In Power Query it is always 3 statements, it is either:
    if then else if
    or
    if then else

    if [some field] = 1 then "It works"
else if [Some field] = 2 then "It doesn't work" 
else if [some field] = null then "It is empty"
else null


    You must have that final else. It isn't like Excel where if you omit the FALSE condition it still works. It will throw an error in Power Query.

    And it is case sensitive. if/then/else, not If/Then/Else, or IF/THEN/ELSE. Those will throw errors.

    The issue is you cannot reference cells in Power Query, so there is no AS6 or AZ4. 

    You need to share data with us and explain what you are trying to do. It may be PQ isn't the right place for it, could be a DAX measure. Could be a combination. 

    Never approach a Power BI project with "how do I translate this long Excel formula into Power BI?" It is always "How can I create this output in Power BI with this data?"

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.