Custom rounding

[Mic1979]

Dear all

I am using the following custom rounding as I cannot use the standard rules to round numbers for my scope:

RoundedOEMVolumes = Table.AddColumn(Grouped_Table, "OEM_Volumes_Rounded", each
if Number.Mod([OEM_Volumes], 1) >= Rounding_Factor
then Number.RoundDown([OEM_Volumes]) + 1
else Number.RoundDown([OEM_Volumes]), type number),

RoundedDISTRIBUTORVolumes = Table.AddColumn(RoundedOEMVolumes, "DISTRIBUTOR_Volumes_Rounded", each
if Number.Mod([DISTRIBUTOR_Volumes], 1) >= Rounding_Factor
then Number.RoundDown([DISTRIBUTOR_Volumes]) + 1
else Number.RoundDown([DISTRIBUTOR_Volumes]), type number)

Rounding_Factor is a parameter defining how to round the number

I have two questions:

1: how to combine this two steps in one?

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.

Thanks a lot for your support here. 

[ZhangKun]

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.

[Mic1979]

Thanks..

I have tested and I accept the solution.

Great community.

[AmiraBedh · Accepted solution]

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"}
)

[Mic1979]

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.

[v-nmadadi-msft]

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 Learn

Table.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

[Mic1979]

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.