Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
scott_Abaana
Helper I
Helper I

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?

1 ACCEPTED SOLUTION

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

11 REPLIES 11
edhans
Super User
Super User

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.

edhans_0-1601934880537.png

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

 

 

 

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

@Jimmy801 Yeah it is tedious, (hence the Question) 🙂

 

This type of problem comes up a lot in my current Build. 

 

Will have a look at the code and see how it works for me. 

 

Many thanks.

 

Ps is there a built option in PQ that does the same/similar thing?

Can you provide data @scott_Abaana - I am sure we can get you some code that works. As you are new to Power Query, I prefer to do it via the user interface if possible as that is the easiest to understand and edit vs custom M code that, while it may work and work wonderfully, you must fully understand it to edit it even slightly. If done through the ribbon and menus though it is easier for even more seasoned users to edit and understand.

 

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans 

 

OK so I have uplaoded a small sample file here Sample Power Query 

 

As I went through the problem for me was a little clearer. As you stated I want to use the ribbon as much as possible so I dont mess up the code. As I get more familiar with it I find I can edit and tweak it.

 

But my challenge was that when you use the ribbon for a conditional column it does not allow you to put in a formula for the output.  This is why I ran three columns each calculating the various formulaes needed, and then select which one I want.  I guess the solution probably is to run and if else formula, but Not clear if you can do this from the ribbon.

 

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Yeah I think it pretty much. I am doing more complex thing in Excel its just getting used to the new language. 

 

My full Power Query has loads of levels to it and takes 2-3 mins to update, but I think its partly becaues I am running two many calculations. 

 

@edhans   Did this file work for you straight away? Because the Data was loaded from itself? 

No. It was linked to a file on your hard drive. I pointed it back to the table you had in one of the spreadsheets so I could work on it. If the original source file isn't available. Power Query errors out immediately. Check ou the Source line now - it is to an Excel sheet (or table?) rather than an XLSX file on a hard drive.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thats actually really helpful. I think I will build some of my tables in this way. I have also just learnt about the parameters which can be used as quick edits for the file locations and if I can get these all set up I think It will make it easier to relocate PQ

@edhans I have created a sample file. I am just rying to figure out how to upload

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors