Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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]
Best Regards
Maggie
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]
Best Regards
Maggie
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
Best regards
Maggie
Hi,
How do you decide which number should be rounded to 1 and which one to 0?
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 |
Hi @earsuyheng
From your screenshot, i don't see "total sum of each proportion" and "otal number".
Could you clear me ?
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |