Forum Discussion

scott_Abaana's avatar
scott_Abaana
Helper I
5 years ago
Solved

Combining Columns or replacing

Hi,  So I am building a reporting system based on power Query and I am just learning the basics at the moment.  Its pretty complex when put together. As part of this I am dealing with different payer types represented in a Frequencey Column (Monthly, Quarterly, Yearly) and also in a Type column (Cash, DD, Cheque) etc

 

Sometimes I need to process calculation differently based on types, EG Applying a formula to Yearly - Cash and different to Monthly - DD.  Currently if I am doing this "planned" I create two new calculcated columns and then combine into a new final clumn, based on whether they are Yearly Cash or Monthy DD. Then I have to remove/hide the two other columns "unwanted" columns. Is there an easier way to do this espeically as sometimes I find myself going back to fix a formula. By this I mean, you forgot to do the Yearly Cash, and you have already used the the new calculated field, so dont want to remove it. Is it possible to apply the formulas over a column(eg  for all yearly cash) which allows that same column to work through your future queries?

Need Help
  • edhans's avatar
    edhans
    5 years ago

    I'd really call that doing it from the Ribbon scott_Abaana - you want several if/then/else constructs. If you can do nested IF() functions in Excel (or IFS()) then you can do this.

    From the ribbon, go to Add Columns, then Custom Column, and type in this formula:

    if [FREQ] = "Cash" then [Amount] else if [FREQ] = "Yearly" then [Amount] else if [FREQ] = "Monthly" then [Amount] * 12 else if [FREQ] = "Quarterly" then [Amount] * 4 else null

    So intead of creating all of the columns, you just do the math on the [Amount] field.

     

    The if/then/else construct is a bit different than Excel.

    • the keywords if, then, and else are always lowercase
    • All three are required. Excel lets you leave the last one out and you can do =IF(A1=5,3) and that will return a blank if A1 doesn't equal 5 in Excel. In Power Query, if [Field] = 5 then 3 will return an error because else is missing.  I used else null for your final answer, but you can return the 9999999 if you want.

    Here is your workbook back. Look at my Single if then else query.

     

11 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    I am not 100% sure I understand what you are asking scott_Abaana but see if this helps. In Power Query, all steps are tracked through the Applied Steps pane.

    You can go back up to previous steps and redo/modify them. As long as you are careful, all changes will just work. If you break something, like rename a column in the 3rd step that the 5th step needs, you just go to the 5th step and tell it to reference the new column name in the Power Query formula bar.

     

    If this is not what you are asking, can you provide some screenshots and/or data of the issue with a bit more information?

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum

    • scott_Abaana's avatar
      scott_Abaana
      Helper I
      5 years ago

      Yes I understand what you have said, but I am not sure I have explained well.

       

      Will Try and explain a little better

       

      To Start lets Say you have 5 Columns of Data A-E

       

      You want to peform some calculations on Columns C and D based on the 3 different values in E. (ie, if E is monthly you would applies different formula, than if it was Yearly)

       

      So I have been creating:

      • F - Conditonal calaulatuon based on E Criteria 1
      • G - Conditonal calaulatuon based on E Criteria 2
      • H - Conditonal calaulatuon based on E Criteria 3
      • I - Combiming the resulst of F,G,H based on the values in E
      • Finally Hide F,G,H

      I know you could put in a if /else, but the nice part of PQ is that you can do it in steps rather than large nested formulas.

       

      So my Question: Is this is the best approach. 

       

      Eg I read something that suggested you could replace values in a column. Could you.

       

      • create F, Conditonal calaulatuon based on E Criteria 1

      • Replace in F, Conditonal calaulatuon based on E Criteria 2 etc

       

      This would also be helpful if you forget a Criteria.

       

       

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello scott_Abaana 

         

        your approach is kinda tedious. You can use a Table.ReplaceValue to do everything in one step. I made a quick example for you

        
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdgFiIwMjA6VYHWwShpZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", Int64.Type}}),
    ChangeE = Table.ReplaceValue
    (
        #"Changed Type",
        each [E],
        (row)=>
        let 
            F = if row[E]=2020 then "twentytwenty" else "twentynineteen",
            G = if row[E]=2020 then "laterthen2019" else "laterthen2018",
            H = if row[E]=2020 then "higher" else "lower",
            Combine = if row[E]=2020 then Text.Combine({F,G,H}) else Text.Combine({H,F,G})
        in 
            Combine,
        Replacer.ReplaceValue,
        {"E"}
    )
in
    ChangeE

         

        Copy paste this code to the advanced editor in a new blank query to see how the solution works.

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy