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
mramstead1
Frequent Visitor

How to Create Multiple Custom Columns with Different Formulas in One Applied Step?

Hello,

 

I'll preface this by saying I am an intermediate Excel user who is still relatively new to both M-Code and DAX and I just started using Power BI.

 

I am currently working on building a query to our P&L statement so that the data is usable in PBI. Our organization uses positive numbers for both our income and expense accounts, which means I need to make all expense lines negative numbers in order to get a correct total on my dashboard. I was able to do this by first creating a column that would return "TRUE" for Income accounts and "FALSE" for Expense accounts (= Table.AddColumn(#"Reordered Columns", "Income/Expense", each if [AccountTypeDescription] = "INCOME" then true else false)).

 

I then used this column as a backbone in order to transform my data rows, keeping Income accounts positive numbers and changing Expense accounts to negative numbers (= Table.AddColumn(#"Renamed Columns", "4Y Prior Total", each if([#"Income/Expense"]) then [CurrentPeriod_2] else -[CurrentPeriod_2])). 

 

This formula above is working great, however, I have 30 coumns of P&L data that need this transformation (CurrentPeriod_1 - CurrentPeriod_30). This ends up creating 30 additional Applied Steps which might slow down the query and/or be hard to navigate if something breaks.

 

Is there a way to apply the transformation I need to all 30 columns in one or two applied steps? The formula stays the same for each column except the number after the underscore since the columns are all named CurrentPeriod_## (ex. CP_1, CP_2, CP_3... etc).

 

Any help or insight would be greatly appreciated! Thank you in advance!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @mramstead1 ,

 

My first instinct would be to unpivot your [CurrentPeriod_X] columns.

Multi-select all columns that are not [CurrentPeriod_X], then on the Transform tab, go to Unpivot Columns > Unpivot Other Columns.

This will normalise your data for correct data model use, and will mean you only need to perform your polarity change on one column.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @mramstead1 ,

 

My first instinct would be to unpivot your [CurrentPeriod_X] columns.

Multi-select all columns that are not [CurrentPeriod_X], then on the Transform tab, go to Unpivot Columns > Unpivot Other Columns.

This will normalise your data for correct data model use, and will mean you only need to perform your polarity change on one column.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you very much @BA_Pete!

 

Your solution worked great and I now understand how pivoting and unpivoting works within the query (somewhat). Have a great rest of your day!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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