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!
