cancel
Showing results for
Did you mean:
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?

Highlighted
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
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

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

Frequent Visitor

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

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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 6 members 846 guests
Recent signins: