cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
earsuyheng Frequent Visitor
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 CColumn DColumn EExcel 
 UnitMonth YearUnit AllocatAcc RoundAcc RoundUnit AllocatinFormula Text
Room25313652.123287671202=$C$4*D4/$E$4
  28 1.917808219422=$C$4*D5/$E$4
  31 2.123287671642=$C$4*D6/$E$4
  30 2.054794521862=$C$4*D7/$E$4
  31 2.1232876711082=$C$4*D8/$E$4
  30 2.05479452112102=$C$4*D9/$E$4
  31 2.12328767115123=$C$4*D10/$E$4
  31 2.12328767117152=$C$4*D11/$E$4
  30 2.05479452119172=$C$4*D12/$E$4
  31 2.12328767121192=$C$4*D13/$E$4
  30 2.05479452123212=$C$4*D14/$E$4
  31 2.12328767125232=$C$4*D15/$E$4
    25  25 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Round decimal number to whole number in power query

Hi @earsuyheng

How about creating measures?

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],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"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"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 1, 1)
in
    #"Added Index"

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]

2.png

 

Best Regards

Maggie

9 REPLIES 9
Community Support Team
Community Support Team

Re: Round proportion to integer value in power query

Hi @earsuyheng

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

Could you clear me ?

 

Best Regards

Maggie

Super User
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
earsuyheng Frequent Visitor
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
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 
 UnitMonth YearUnit AllocatAcc RoundAcc RoundWhole Unit  
Room25313652.123287671202=$C$4*D4/$E$4
  28 1.917808219422=$C$4*D5/$E$4
  31 2.123287671642=$C$4*D6/$E$4
  30 2.054794521862=$C$4*D7/$E$4
  31 2.1232876711082=$C$4*D8/$E$4
  30 2.05479452112102=$C$4*D9/$E$4
  31 2.12328767115123=$C$4*D10/$E$4
  31 2.12328767117152=$C$4*D11/$E$4
  30 2.05479452119172=$C$4*D12/$E$4
  31 2.12328767121192=$C$4*D13/$E$4
  30 2.05479452123212=$C$4*D14/$E$4
  31 2.12328767125232=$C$4*D15/$E$4
    25  25 
Community Support Team
Community Support Team

Re: Round decimal number to whole number in power query

Hi @earsuyheng

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],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"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"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Unit Allocat", each [Unit]*[#"Month "]/[Year]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "acc sum", each let Room=[Room], Index=[Index] in
                        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"}),
    #"Added Custom3" = Table.AddColumn(#"Replaced Value", "Whole Unit ", each [Round.1]-[Round.2])
in
    #"Added Custom3"

Finally get the result as below

1.png

 

Best regards

Maggie

 

Community Support Team
Community Support Team

Re: Round decimal number to whole number in power query

Hi @earsuyheng

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
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
Community Support Team

Re: Round decimal number to whole number in power query

Hi @earsuyheng

How about creating measures?

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],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"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"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 1, 1)
in
    #"Added Index"

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]

2.png

 

Best Regards

Maggie

earsuyheng Frequent Visitor
Frequent Visitor

Re: Round decimal number to whole number in power query

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 6 members 846 guests
Please welcome our newest community members: