Forum Discussion
Post PatronRounding to the nearest Value Based on Values from another table.
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"
Impactful IndividualHi 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-another-table/m-p/2496166#M75583")),
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-another-table/m-p/2496166#M75583")),
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
Post PatronThank 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!