Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have been able to achieve the first part like so:
by inserting this code:
= Table.ReplaceValue(#"Source", each [SUP1], each [SUP1]*[Operator], Replacer.ReplaceValue, {"SUP1"})
I am able to change the value of SUP1 as below:
However I want to change the values of a range of columns, not just the SUP1 column.
How do I change the replace value code to affect all the Columns in the table e.g SUP2 SUP3 SUP4...
Thanks!
Solved! Go to Solution.
Hi @kgiboin ,
In your current data format, you would not be able to apply this to multiple columns at the same time - you would need to create a new replace step for each [SUP2], [SUP3] etc.
However, you should really unpivot your SUP columns (multi-select [PART NUMBER] and [Operator] then Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns).
This will firstly allow you to perform your multiplication replacement step generically on a single column ([Value]), but also restructure your data into the most efficient format for reporting.
Pete
Proud to be a Datanaut!
Unpivoting is definitely the best way to go but it is possible to do multiple columns simultaneously even without explicitly referencing each one if you do some fancy row transformations.
Table.FromRecords(
Table.TransformRows(
#"Previous Step Name",
(row) => Record.TransformFields(
row,
List.Transform(
List.Skip(Record.FieldNames(row), 2),
(name) => {name, each Record.Field(row, name) * row[Operator]}
)
)
)
)
I found another way to do it without unpivoting:
= Table.FromRecords(Table.TransformRows(#"previous step name",(row) => Record.TransformFields(row,List.Transform(List.Skip(Record.FieldNames(row), [enter the nth column you want the formula to start]),(name) => {name, each Record.Field(row, name) * row[#"Operator"]}))))
Hi @kgiboin ,
In your current data format, you would not be able to apply this to multiple columns at the same time - you would need to create a new replace step for each [SUP2], [SUP3] etc.
However, you should really unpivot your SUP columns (multi-select [PART NUMBER] and [Operator] then Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns).
This will firstly allow you to perform your multiplication replacement step generically on a single column ([Value]), but also restructure your data into the most efficient format for reporting.
Pete
Proud to be a Datanaut!
Unpivoting is definitely the best way to go but it is possible to do multiple columns simultaneously even without explicitly referencing each one if you do some fancy row transformations.
Table.FromRecords(
Table.TransformRows(
#"Previous Step Name",
(row) => Record.TransformFields(
row,
List.Transform(
List.Skip(Record.FieldNames(row), 2),
(name) => {name, each Record.Field(row, name) * row[Operator]}
)
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.