Looking for a way to change the column values based off another column but the columns I want to change were created dynamically so it's not very simple in power query. Here is the question I asked before to get to this part and now I am stuck again. Add Custom and Dynamic columns
Table before
| True/False | DynamicColName1 | DynamicColName2 | ... | DynamicColNameX |
|---|---|---|---|---|
| True | 50 | 60 | ... | ### |
| False | 50 | 60 | ... | ### |
| False | 50 | 60 | ... | ### |
| False | 50 | 60 | ... | ### |
| True | 50 | 60 | ... | ### |
The ... is just to show the columns can be X long because of the dynamic range of the headers. The ### represents it can be whatever value. Doesn't matter in this case.
This is what I want the table to show now. If True/False col = False then the values in the dynamic columns change to 0. If True then stay the same
Table after
| True/False | DynamicColName1 | DynamicColName2 | ... | DynamicColNameX |
|---|---|---|---|---|
| True | 50 | 60 | ... | ### |
| False | 0 | 0 | ... | 0 |
| False | 0 | 0 | ... | 0 |
| False | 0 | 0 | ... | 0 |
| True | 50 | 60 | ... | ### |
Add index, Unpivot, add an if/then statement, then repivot
Full sample code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,List.RemoveFirstN(Table.ColumnNames(Source),1)),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"True/False", type logical}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"True/False", "Index"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [#"True/False"] then [Value] else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in #"Removed Columns1"