cancel
Showing results for
Did you mean:
earsuyheng Frequent Visitor

## Round decimal number to whole number in power query

Hi All,

I am serching for solution in power query to round decimal number to whole number

Below is the example in excel and I want to do it in power query with M code that performance is good.

Thanks for your time and help

Regards,

Heng

 Column C Column D Column E Excel Unit Month Year Unit Allocat Acc Round Acc Round Unit Allocatin Formula Text Room 25 31 365 2.123287671 2 0 2 =\$C\$4*D4/\$E\$4 28 1.917808219 4 2 2 =\$C\$4*D5/\$E\$4 31 2.123287671 6 4 2 =\$C\$4*D6/\$E\$4 30 2.054794521 8 6 2 =\$C\$4*D7/\$E\$4 31 2.123287671 10 8 2 =\$C\$4*D8/\$E\$4 30 2.054794521 12 10 2 =\$C\$4*D9/\$E\$4 31 2.123287671 15 12 3 =\$C\$4*D10/\$E\$4 31 2.123287671 17 15 2 =\$C\$4*D11/\$E\$4 30 2.054794521 19 17 2 =\$C\$4*D12/\$E\$4 31 2.123287671 21 19 2 =\$C\$4*D13/\$E\$4 30 2.054794521 23 21 2 =\$C\$4*D14/\$E\$4 31 2.123287671 25 23 2 =\$C\$4*D15/\$E\$4 25 25
1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Round decimal number to whole number in power query

As far as i known, for performance:

creating measure>add custom columns in power query>creating calculated columns

After creating an index column in Power Query,

```let
Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\1\1.1\1.1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Room", type text}, {"Unit", Int64.Type}, {"Month ", Int64.Type}, {"Year", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Room", Order.Ascending}, {"Unit", Order.Descending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Unit", "Year"}),
in

Then go to Home->Modeling->New Measure

```Unit_Allocat = MAX(Sheet1_copy[Month ])*MAX(Sheet1_copy[Unit])/MAX(Sheet1_copy[Year])

acc-sum1 = SUMX(FILTER(ALLEXCEPT(Sheet1_copy,Sheet1_copy[Room]),[Index]<=MAX([Index])),[Unit_Allocat])

acc-sum2 = SUMX(FILTER(ALLEXCEPT(Sheet1_copy,Sheet1_copy[Room]),[Index]<MAX([Index])),[Unit_Allocat])

interger-1 = ROUND([acc-sum1],0)

interger-2 = ROUND([acc-sum2],0)

whole unit = [interger-1]-[interger-2]``` Best Regards

Maggie

9 REPLIES 9 Community Support Team

## Re: Round proportion to integer value in power query

From your screenshot, i don't see "total sum of each proportion" and "otal number".

Could you clear me ?

Best Regards

Maggie Super User

## Re: Round proportion to integer value in power query

Hi,

How do you decide which number should be rounded to 1 and which one to 0?

earsuyheng Frequent Visitor

## Re: Round proportion to integer value in power query

Dear Maggie,
For example under unit column the total sum I refer is 8 and each proportoin I mean
each decimal number in unit column. Thus, if I around each decimal number to whole
Whole number the sum of it will be 12 but the total sum is 8.
Thus I want to do in power query.
Thanks and regards,
Heng
earsuyheng Frequent Visitor

## Re: Round proportion to integer value in power query

Hi Ashish,

For example, below I do in excel but I want to do it in power query. I want the result  in column whole unit.

Thanks and regards,

Heng

 Excel Unit Month Year Unit Allocat Acc Round Acc Round Whole Unit Room 25 31 365 2.123287671 2 0 2 =\$C\$4*D4/\$E\$4 28 1.917808219 4 2 2 =\$C\$4*D5/\$E\$4 31 2.123287671 6 4 2 =\$C\$4*D6/\$E\$4 30 2.054794521 8 6 2 =\$C\$4*D7/\$E\$4 31 2.123287671 10 8 2 =\$C\$4*D8/\$E\$4 30 2.054794521 12 10 2 =\$C\$4*D9/\$E\$4 31 2.123287671 15 12 3 =\$C\$4*D10/\$E\$4 31 2.123287671 17 15 2 =\$C\$4*D11/\$E\$4 30 2.054794521 19 17 2 =\$C\$4*D12/\$E\$4 31 2.123287671 21 19 2 =\$C\$4*D13/\$E\$4 30 2.054794521 23 21 2 =\$C\$4*D14/\$E\$4 31 2.123287671 25 23 2 =\$C\$4*D15/\$E\$4 25 25 Community Support Team

## Re: Round decimal number to whole number in power query

Make a test with the following dataset

 Room Unit Month Year Room1 25 31 365 Room1 28 Room1 31 Room1 30 Room1 31 Room1 30 Room1 31 Room1 31 Room1 30 Room1 31 Room1 30 Room1 31 Room2 27 34 366 Room2 35 Room2 36 Room2 37

Import to power bi, write the following code in Advanced editor of Edit queries

```let
Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\1\1.1\1.1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Room", type text}, {"Unit", Int64.Type}, {"Month ", Int64.Type}, {"Year", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Room", Order.Ascending}, {"Unit", Order.Descending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Unit", "Year"}),
List.Sum(Table.SelectRows(#"Added Index", each [Index]<=Index and [Room]=Room)[Unit Allocat])),
#"Inserted Rounding" = Table.AddColumn(#"Added Custom1", "Round", each Number.Round([Unit Allocat], 0), type number),
#"Inserted Rounding1" = Table.AddColumn(#"Inserted Rounding", "Round.1", each Number.Round([acc sum], 0), type number),
#"Added Custom2" = Table.AddColumn(#"Inserted Rounding1", "acc sum2", each let Room=[Room], Index=[Index] in
List.Sum(Table.SelectRows(#"Added Index", each [Index]<Index and [Room]=Room)[Unit Allocat])),
#"Inserted Rounding2" = Table.AddColumn(#"Added Custom2", "Round.2", each Number.Round([acc sum2], 0), type number),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Rounding2",null,0,Replacer.ReplaceValue,{"Round.2"}),
in

Finally get the result as below Best regards

Maggie Community Support Team

## Re: Round decimal number to whole number in power query

If the method in my last post is slow in your dataset, please let me know.

It can be achieved by creating measures or columns with DAX functions.

Best Regards

Maggie

earsuyheng Frequent Visitor

## Re: Round decimal number to whole number in power query

Hi Maggie,

Really thanks for your help and it work out but like you said , the performance is very slow.

Could you help amend the M code so that the perform well as I have large data set.

I am seraching in the web and there is some recommend that imporve M code but I dont know how to integrate into my solution

as i perfer to do it power query.

I found out one post from BIccountant but don't know how to use it into my solution.

https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

Could you help?

Thanks and regards,

Heng Community Support Team

## Re: Round decimal number to whole number in power query

As far as i known, for performance:

creating measure>add custom columns in power query>creating calculated columns

After creating an index column in Power Query,

```let
Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\1\1.1\1.1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Room", type text}, {"Unit", Int64.Type}, {"Month ", Int64.Type}, {"Year", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Room", Order.Ascending}, {"Unit", Order.Descending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Unit", "Year"}),
in

Then go to Home->Modeling->New Measure

```Unit_Allocat = MAX(Sheet1_copy[Month ])*MAX(Sheet1_copy[Unit])/MAX(Sheet1_copy[Year])

acc-sum1 = SUMX(FILTER(ALLEXCEPT(Sheet1_copy,Sheet1_copy[Room]),[Index]<=MAX([Index])),[Unit_Allocat])

acc-sum2 = SUMX(FILTER(ALLEXCEPT(Sheet1_copy,Sheet1_copy[Room]),[Index]<MAX([Index])),[Unit_Allocat])

interger-1 = ROUND([acc-sum1],0)

interger-2 = ROUND([acc-sum2],0)

whole unit = [interger-1]-[interger-2]``` Best Regards

Maggie

Highlighted
earsuyheng Frequent Visitor

## Re: Round decimal number to whole number in power query

Dear Maggie,
Thanks for your time and contribution.
Regards,
Heng