Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Transform Excel column formulas to Power Query query (or Power BI)

I'm trying to create two new columns in Power Query (or I could use Power BI Desktop) which replicate an Excel formula.  

The two columns are titled 'Site' and 'Period' (columns A and B respectively).  

 

The Excel formula which I am trying to replicate is in column A (Site):

=IF(OR(C4="Site:",C4=""),"",IF(C4="Week","Site",IF(C1="Site:",D1,A3)&""))

 

The Excel formula which I'm trying to replicate for column B (Period):

=IF(OR(C4="Site:",C4=""),"",IF(C4="Week","Period",IF(F1="Period:",G1,B3)&""))

 

So that the returned columns should look like following:  

 

 

How would I do this?  Please help.  Thank you.

Need Help
Tutorial Requests

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    What is your data structure in power bi?

    In your data structure of excel, "Site" and "Period" is defined by the formula, but it should be column header.

    It couldn't be defined by the formula, is it a "value" not the column header in power bi? 

    And how to get D1,A3 or G1,B3 , are they in the same table together in power bi?

    The logic of your formula is simple, and please share your sample pbix file or some screenshots of data view in power bi.

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Lin (@,

      I've attached the Excel file this originates from.  I'm trying to take this to a Power BI (or Power Query) solution .  This shows the file with the computed columns I'm looking to move to Measure or solution that will utilize Power Query to create these columns rather than doing it in Excel once the 'export' tabs have been combined.  I also need to remove the rows that contain the text 'Site:' and 'Period' after the (in this case calculated columns) are created.  In other words, the values for 'Site:' and 'Period:' are used to create the cacluated columns and then the rows that are used to create them would be removed.  So the values for Site and Period would need to be copied from their corresponding row and not just referenced.  Thanks for your help!

       

      vr,

      namdron

       

      Production Payroll Data Solution with Site IDs.xlsx

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Anonymous 

        For your case, I suggest you do it in Excel.

        If this is raw data, how do you import them into power bi?

        just import table "tool"? if so, the sort of column value may be changed, and how to match the 'Site:' and 'Period:' for the raw data? Your raw data structure is irregular, for your special case, It's very easy to implement in excel but is very troublesome in power bi.

         

        Best Regards,

        Lin