Forum Discussion
Custom rounding
- 1 year ago
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"} )
If you want to combine your code, you can useTable.AddColumn once 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"}
)
- v-nmadadi-msft1 year agoCommunity Support
Hi Mic1979,
Thanks for reaching out to the Microsoft fabric community forum.
Thank you AmiraBedh for you valuable contribution.
For a deeper understanding of the mentioned functions, please refer to the following two documents.
Table.AddColumn - PowerQuery M | Microsoft LearnTable.ExpandRecordColumn - PowerQuery M | Microsoft Learn
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards
- Mic19791 year agoPost Partisan
Hello v-nmadadi-msft
it was very useful, but I had a problem with the excel installed on my computer, and I could not test it.
I would like to test it before accept this as solution, also to be of help for other users that may expect just to copy and paste it.
Thanks.
- Mic19791 year agoPost Partisan
Thanks a lot.
Just a question. How is the position of the different value preserved?
In other words, Am I sure that when I recombine everything in the table, the numbers are put in the original positions?
Thanks.