I have a table - each row has columns for percentages of different categories plus a column for total value, and I want to calculate the value for each category by multiplying the total value by each category percentage.
I have a table, and, for each of the other columns ("Market Cap.Large", "Market Cap.Medium" etc. - all except "Symbol") I want to multiply it by the "Current Value" column. I can do it manually and add a column for each (the #"Added Custom" line below), but the number of columns may increase and the titles may change, so I don't want to have to input a custom column for each.
Is there a recursive function to create a new column for each of the existing ones, and multiply it by "Current Value" - I'm guessing it would involve creating a parameter for the column name, but I'm not knowledgeable enough on how to do it.
let
Source = #"Portfolio %",
#"Merged Queries" = Table.NestedJoin(Source,{"Symbol"},#"Current Output Aggregated",{"Symbol"},"Current Output Aggregated",JoinKind.Inner),
#"Expanded Current Output Aggregated" = Table.ExpandTableColumn(#"Merged Queries", "Current Output Aggregated", {"Current Value"}, {"Current Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Current Output Aggregated", "Market Cap.Giant Value", each [Market Cap.Giant]*[Current Value])
in
#"Added Custom"
Hi Alex,
Welcome to our forum!
First, I have to mention that it's much better to upload a file with your sample data instead of sending a picture of your data.
What you want is possible, but it's not the best way to do things, I guess you want to add a variable number of columns because you don't know another way to view the results your way.
Remember, a very important thing in designing an application is separating the data sheet and the data visualization. Processing the data and viewing the results should not be always the same thing.
If you reformat the data to a tabular format, you will be able to visualize it exactly as you want, with much easier steps, no need to complicate things.
Check the attached file, there are 3 ways to get to the results you want.
The first method is to Unpivot all columns except the first 2, all you need to do is to add a column to multiply the current value with the percentages, and another column to create the new column names. Pivoting back the 2 columns will give you the results you can use in a pivot table that can be organized to display as you want. (the query name for this method is Table1)
The easiest method is to unpivot al columns except first 2, multiply the current with the percentage values column , then visualize the results with a pivot table, see the query named BetterFormat.
Your original request can be achieved with the following query:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
headers = List.Skip(Table.ColumnNames(Source),2),
AddNewColumns = List.Accumulate({1..List.Count(headers)}, Source, (state, current) => Table.AddColumn(state, "New Value " & headers{current-1}, each [#" ▼ $ Current Value"] * Expression.Evaluate("[#""" & headers{current-1} & """]", [_=_]) ))
in
AddNewColumns
It's not as easy as the previous ways, and it's not as flexible as the BetterFormat query that I recommend.
Hi Catalin,
Yes I see using Unpivot is a batter method, thanks.
