cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan_b_fiting
Post Patron
Post Patron

Merge Queries - Expanded Table uses a formula

Hello Community - 

 

I am in need of some assistance.  I have a step in Power query where I am merging queries.  Once merged, I am expanding a single column from that merged query which is a 'Bonus Rate'.  The end value needs to be a Bonus $ Amount.  Instead of having to create another step that would create a Custom Column to simply take the Salary ($) * Bonus Rate (which is the expanded column from the merged query), is there a way to nest the custom column step into the 'Expand Merge Queries' step? 

 

Currently My query step is this:

= Table.ExpandTableColumn(#"Merge Bonus Data", "Bonus", {"Rate with Vacancy Hedge"}, {"Bonus Rate"})

My next step would be to create the custom column, but I want to know if there is a way that I can take the step from above and tweak it to hace the "Rate with Vacancy Hedge" * Salary($) in the same step?

 

I have several columns where I need to do this, so ideally rather than having to create a custom column for each one, I would like to consolidate those steps.

 

Thanks in advance for any insights!

Ryan F

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hello @ryan_b_fiting ,
you cannot include the calculation in the Expand-step/formula.
But if you are sure that there will be always just one row in the merged column, then instead of expanding the table, you could reference the required field in the unexpanded table instead like so:

Table.AddColumn(#"Merge Bonus Data", "myCalculation", each  [Bonus]{0}[#"Rate with Vacancy Hedge"] * [#"Salary($)"])

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

1 REPLY 1
ImkeF
Super User
Super User

Hello @ryan_b_fiting ,
you cannot include the calculation in the Expand-step/formula.
But if you are sure that there will be always just one row in the merged column, then instead of expanding the table, you could reference the required field in the unexpanded table instead like so:

Table.AddColumn(#"Merge Bonus Data", "myCalculation", each  [Bonus]{0}[#"Rate with Vacancy Hedge"] * [#"Salary($)"])

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors