Forum Discussion
Custom rounding
If you want to combine your code, you can use
Table.AddColumnonce and apply the rounding logic to both columns within the same transformation :RoundedVolumes = Table.AddColumn(Grouped_Table, "Rounded_Volumes", each [ OEM_Volumes_Rounded = if Number.Mod([OEM_Volumes], 1) >= Rounding_Factor then Number.RoundDown([OEM_Volumes]) + 1 else Number.RoundDown([OEM_Volumes]), DISTRIBUTOR_Volumes_Rounded = if Number.Mod([DISTRIBUTOR_Volumes], 1) >= Rounding_Factor then Number.RoundDown([DISTRIBUTOR_Volumes]) + 1 else Number.RoundDown([DISTRIBUTOR_Volumes]) ], type record )If you want to expand this record into separate columns, you can use
Table.ExpandRecordColumn:ExpandedRoundedVolumes = Table.ExpandRecordColumn(RoundedVolumes, "Rounded_Volumes", {"OEM_Volumes_Rounded", "DISTRIBUTOR_Volumes_Rounded"})If you want to improve the performance, you can work with lists instead of the entire table because it avoids you loading the entire table into memory for each operation.
oemVolumes = Table.Column(Grouped_Table, "OEM_Volumes") distributorVolumes = Table.Column(Grouped_Table, "DISTRIBUTOR_Volumes") roundValue = (value) => if Number.Mod(value, 1) >= Rounding_Factor then Number.RoundDown(value) + 1 else Number.RoundDown(value) roundedOEMVolumes = List.Transform(oemVolumes, roundValue) roundedDistributorVolumes = List.Transform(distributorVolumes, roundValue) RoundedVolumesTable = Table.FromColumns( Table.ToColumns(Grouped_Table) & {roundedOEMVolumes, roundedDistributorVolumes}, Table.ColumnNames(Grouped_Table) & {"OEM_Volumes_Rounded", "DISTRIBUTOR_Volumes_Rounded"} )
1: how to combine this two steps in one?
As AmiraBedh said, you can first generate the record value in Table.AddColumn and then expand it (click the expand button or use Table.ExpandRecordColumn function to expand the record). But this is not necessary, the reason I will tell you in the next question.
2: this operation is taking a long, probably due to the fact I have a lot of rows. I was wondering if it makes sense to act only on the values, instead of getting the whole table in the memory, maybe using the lists (list.zip to preserve the positions), and then putting again the values in the table.
In fact, since the calculations here are simple, adding many columns will not have much impact on performance.
When you add some columns, you may think that Power Query will load the entire table each time you add a column, but this is not the case. Power Query is like a pipeline. It uses streaming calculations by default. It loads some rows, adds columns to the rows, and then uploads them to the DAX engine (or Power Pivot, or Power BI).
If you add three columns, it actually loads some rows, adds three columns in turn, uploads to the DAX engine, and continues to load, add, and upload until all data is loaded.
Back to the whole question, since both parts are the same calculation rules, they can be abstracted into a function. You can put it inside the query (it can only be used in the query where the function is located), or put it separately in the query (it can be used anywhere in the current file).
You can view the attachment to see the specific usage.
Thanks..
I have tested and I accept the solution.
Great community.
