Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

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

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

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

Dear Maggie,
Thanks for your time and contribution.
Regards,
Heng
v-juanli-msft
Community Support
Community Support

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

 

Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 
v-juanli-msft
Community Support
Community Support

Hi @earsuyheng

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

Could you clear me ?

 

Best Regards

Maggie

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.