cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roncruiser
Helper V
Helper V

Rounding to the nearest Value Based on Values from another table.

Maybe I can gain traction here from a dedicated Power Query Expert as I'd like to do this in Power Query.

I'd like to round to the nearest value based on values from another table and column.
The value may fall between two values, and I would like the value to round to the nearest one.

Table 1

Location Round to Nearest Value
A2
B3
C29
D15
E20
F27
G11
H7
I9

 

Table is the reference table used to round to the nearest value.
If the value from the table above falls between the values below, the round to the nearest value.
Table 2

0
5
10
20
25
30


Table 3 is the desired output.

For example location A Value 2 falls between 0 and 5, and is rounded to 0.

Location F Value 27 falls between 25 and 30, and is rounded to 25.
Table 3

Location ValueRounded to Nearest
(Return this Column)
A20
B35
C2930
D1520
E2020
F2725
G1110
H75
I910


I hope I can get some help from a seasoned Power Query expert, as it's a perplexing problem for me.  Maybe for even the season PQ user!

Thanks!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Solution Excel @ https://1drv.ms/x/s!Akd5y6ruJhvhuWkS2pLB0BHnAigv?e=DDSrQv 

Use this for Table 1. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJSitWJVnICsozBLGeQmCWY6QJkGpqCma4gUQMw0w3ENAcz3UEKDMFMDyATIugJZAH1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Low", each Table.Last(Table.SelectRows(Table2, (x)=> x[Column1]<=[Value]))[Column1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "High", each Table.First(Table.SelectRows(Table2, (x)=> x[Column1]>=[Value]))[Column1]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Result", each if Number.Abs([Value]-[Low])>=Number.Abs([Value]-[High]) then [High] else [Low]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Low", "High"})
in
    #"Removed Columns"

 If you need test code for Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMgWThhCOEZSCCBoDebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}})
in
    #"Changed Type"

 

View solution in original post

10 REPLIES 10
KT_Bsmart2gethe
Super User
Super User

Hi @roncruiser,

 

Sorry for late reply. It has been a crazy few days.

 

@Vijay_A_Verma method is very clean and quick if you have large dataset to handle.

 

Below are 2 different approaches:

 1. Embeded calculation method

let
//Get Table 1
Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Power-Query/Rounding-to-the-nearest-Value-Based-on-Values-from-anot...")),
Table1 = Table.PromoteHeaders(Source{0}[Data]),
#"Changed Type" = Table.TransformColumnTypes(Table1,{{"Round to Nearest Value", Int64.Type}}),

//Get Table 2
GetTbl2 = Table.AddColumn(#"Changed Type", "Table 2 Value", each #"Table 2"),

//Add value to Table 2 for calculation
EmbedTbl2 = Table.AddColumn(GetTbl2, "Add Value", each Table.AddColumn([Table 2 Value], "Value", (r)=>[Round to Nearest Value],Int64.Type)),

//Calculate the different between value and table 2 (no negative value)
CalcVar = Table.AddColumn(EmbedTbl2, "Calc", each Table.AddColumn([Add Value], "Result", each Number.Abs([Value]-[Column1]))),

//Get the min number from the calculation
GetMin = Table.AddColumn(CalcVar, "GetMin", each Table.Min([Calc],"Result")),

//Expand the table 2 value (i.e. the nearest value)
#"Expanded GetMin" = Table.ExpandRecordColumn(GetMin, "GetMin", {"Column1"}, {"Column1"})
in
#"Expanded GetMin"

 

2. Traditional method (Group & Merge)

let
//Get Table 1
Source = Web.Page(Web.Contents("https://community.powerbi.com/t5/Power-Query/Rounding-to-the-nearest-Value-Based-on-Values-from-anot...")),
Table1 = Table.PromoteHeaders(Source{0}[Data]),
#"Changed Type" = Table.TransformColumnTypes(Table1,{{"Round to Nearest Value", type text}}),

//Use Fuzzy merge to get table 2 (Value has to be defined in text for merge)
#"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Round to Nearest Value"}, #"Table 2", {"Column1"}, "Table 2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Column1"}, {"Column1"}),

//Change value to number
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Table 2",{{"Round to Nearest Value", type number}, {"Column1", type number}}),

//Calculate different between table 2 group to value
Result = Table.AddColumn(#"Changed Type1", "Result", each Number.Abs([Column1]-[Round to Nearest Value])),

//Group to get min
#"Grouped Rows" = Table.Group(Result, {"Location", "Round to Nearest Value"}, {{"Min", each List.Min([Result]), type number}}),

//Merge previous step - #"Grouped Rows" with step - Result
#"Merged Queries1" = Table.NestedJoin(#"Grouped Rows", {"Round to Nearest Value", "Min"}, Result, {"Round to Nearest Value", "Result"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Grouped Rows", {"Column1"}, {"Column1"})
in
#"Expanded Grouped Rows"

 

Regards

KT

Thank You.  

Yes, it has been a crazy couple weeks!  @Vijay_A_Verma 's solution worked well for me.

I'll give yours a try as well. It looks very interesting from a very top level.  I've not used fuzzy logic merging with Power Query before.  It's good to have options!

Vijay_A_Verma
Super User
Super User

Solution Excel @ https://1drv.ms/x/s!Akd5y6ruJhvhuWkS2pLB0BHnAigv?e=DDSrQv 

Use this for Table 1. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJSitWJVnICsozBLGeQmCWY6QJkGpqCma4gUQMw0w3ENAcz3UEKDMFMDyATIugJZAH1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Low", each Table.Last(Table.SelectRows(Table2, (x)=> x[Column1]<=[Value]))[Column1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "High", each Table.First(Table.SelectRows(Table2, (x)=> x[Column1]>=[Value]))[Column1]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Result", each if Number.Abs([Value]-[Low])>=Number.Abs([Value]-[High]) then [High] else [Low]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Low", "High"})
in
    #"Removed Columns"

 If you need test code for Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMgWThhCOEZSCCBoDebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}})
in
    #"Changed Type"

 

@Vijay_A_Verma 

Thank You.  I'll give it a try.

KT_Bsmart2gethe
Super User
Super User

Hi @roncruiser,

 

I have roughtly wrote a code to get a solution and there is one thing I want to clarify. Please look at below screenshot highlighted location D's nearest value can be either 10 or 20. Would you like to round up to higher value?

KT_Bsmart2gethe_0-1651761499299.png

 

Regards

KT

 

@KT_Bsmart2gethe 

That is a very good question, it depends on the content of the overall data and the value itself.  For now, rounding up would be safer to implement.  

Thank You KT!

BA_Pete
Super User
Super User

Hi @roncruiser ,

 

I guess the question is whether your example data is a fair reflection of the quantity of rounding values you want to use.

If it is, then a simple IF statement would be the smart way to go:

 

if [value] < 2.5 then 0
else if [value] >= 2.5 and [value] < 7.5 then 5
else if...

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete 

My next question would be how to reference table 2 for each row of table 1 to implement the IF statement.
I have not figured that part out yet.  I tried the merge function but that still stumps me.  I'm still going at it.  No luck yet.  Maybe the merge function is not the correct route.

Hi @roncruiser ,

 

My suggestion was to not reference table2 at all.

If table2 only actually contained the 6 values you showed in your example data, then an if statement would be the simplest way to go. Based on the fact that table2 can hold 150-300 conditional values, I'd recommend looking into Vijay's solution.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete 

The quantity of rounding values for the reference table, Table 2 is relatively accurate.  Actual quantity of table 2 values will likely not exceed 150 values in the column.  If it does, it will not exceed more than 300 values in rare cases.

Table one values may exceed more than 500 but no more than 1000 rows.

Thanks for quick the response.  Will respond again soon. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Kudoed Authors