Find a particular value in a range of values and return another value

[Syndicate_Admin]

Very good and thank you for helping me in advance,
I have a column with certain values, and I would like to find in which range this value is located, that range is specified in another table. Finally, I want you to return the value determined for that interval.

I specify data below:

Values column to search:

Table --> Sales invoice lines

Table with the intervals and the result to be returned:

Table --> Escalation Points

The red box marks the interval, and the green box marks the result to be returned.

I would like to perform the query for each value in the "theoretical points" column of the "Sales invoices lines" table, and generate a column in the "Sales invoices lines" table for each value we consult.

For example, the expected result is as follows:

I hope you can help me with this consultation,

Thanks and regards,

[Syndicate_Admin]

Solved

Thank you very much for the help and above all for the patience

Best regards

[Syndicate_Admin]

Good afternoon

For SALES INVOICE LINES, I get the following error:

And for point scaling, the same error appears

I look forward to your help,

Thank you in advance

[lbendlin]

Change "Let"  to "let"

[Syndicate_Admin]

How can I change it?

This is the code I currently have for the 2 tables, including the one you provide.

SALES INVOICE LINES

Let
Source = Sql.Database("192.168.15.30", "Dynamics365_BC130_CU03"),
#"dbo_GLOBAL FUN PART REAL$Sales Invoice Line" = Source{[Schema="dbo",Item="GLOBAL FUN PART REAL$Sales Invoice Line"]}[Data],
#"Other columns removed" = Table.SelectColumns(#"dbo_GLOBAL FUN PART REAL$Sales Invoice Line",{"Document No_", "Sell-to Customer No_", "No_", "Location Code", "Description", "Quantity", "Unit Price", "VAT _", "Line Discount _", "Line Discount Amount", "Amount", "Amount Including VAT", "Purch_ Document No_"}),
#"Renamed Columns" = Table.RenameColumns(#"Other Columns Removed",{{"Document No_", "INVOICE ID"}, {"Sell-to Customer No_", "CUSTOMER ID"}, {"No_", "PRODUCT ID"}, {"Location Code", "WAREHOUSE ID"}, {"Description", "DESCRIPTION"}, {"Quantity", "QUANTITY"}, {"Unit Price", "UNIT PRICE"}}),
#"Inserted Division" = Table.AddColumn(#"Renamed Columns", "Division", each [VAT _] / 100, type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Split",{{"Split", "VAT"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"INVOICE ID", "CUSTOMER ID", "PRODUCT ID", "WAREHOUSE ID", "DESCRIPTION", "QUANTITY", "UNIT PRICE", "VAT", "VAT _", "Line Discount _", "Line Discount Amount", "Amount", "Amount Including VAT", "Purch_ Document No_"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"VAT_", "Line Discount _", "Line Discount Amount"}),
#"Renamed Columns2" = Table.RenameColumns(#"Columns Removed",{{"Amount", "TOTAL WITHOUT VAT"}, {"Amount Including VAT", "TOTAL WITH VAT"}, {"Purch_ Document No_", "PURCHASE PACKING SLIP"}}),
#"Type Changed" = Table.TransformColumnTypes(#"Renamed Columns2",{{"CUSTOMER ID", Int64.Type}, {"WAREHOUSE ID", Int64.Type}, {"QUANTITY", Int64.Type}, {"UNIT PRICE", Currency.Type}, {"VAT", Percentage.Type}, {"TOTAL WITHOUT VAT", Currency.Type}, {"TOTAL INCLUDING VAT", Currency.Type}}),
#"Filtered rows" = Table.SelectRows(#"Type changed", each Text.StartsWith([PRODUCT ID], "A") or Text.StartsWith([PRODUCT ID], "R") or Text.StartsWith([PRODUCT ID], "M") or Text.StartsWith([PRODUCT ID], "O")),
#"Conditional Column Added" = Table.AddColumn(#"Filtered Rows", "Product Type", each if Text.StartsWith([PRODUCT ID], "A") then "GIFTS" else if Text.StartsWith([PRODUCT ID], "M") then "MACHINES" else if Text.StartsWith([PRODUCT ID], "R") then "SPARE PARTS" else if Text.StartsWith([PRODUCT ID], "OR") then "LEISURE CENTER SOLUTIONS" else if Text.StartsWith([PRODUCT ID], "C") then "BIRTHDAY" else if Text.StartsWith([PRODUCT ID], "B") then "BAR" else "ERROR"),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.Binary.FromRows) FromText("tZBNCsJADEavEroeYZI0085y8B/aCm1BUDyCFMQTeB5P5UnMFBW7tNjFJFlk3vfI8ZiQJZxZZM4Sk6ReS0Bi73Sw+tpwCNC0oYZ9qBtoN0soQ7UIxa7ehgqYBMpC95C0kPEpPG53HVkM5ZFIxvXQeR8jPspI8HVkM5ZFIxvXQeR8jPsfI8ZiQJZxZZM4Sk6ReS0Bi73Sw+tpwCNC0oYZ9qBtoN0soQ7UIxa7ehgqYBMpC95C0kPEpPG53HVkM5ZFIxvXQeR8jPsPspPG53HVkM5ZFIxvXQeR8jPsPs. ZYQ6htvV21jfbVpTsDxY9w7UBeBEKfnMxbkJAjDuVbEPnvhiSUjjaMmHxwQpxCUEYI0syKE37HfAT9fwU1xjlxYwVdLzi4YDaBIPNPgqcn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID INVOICE" = _t, #"CUSTOMER ID" = _t, #"PRODUCT ID" = _t, #"WAREHOUSE ID" = _t, DESCRIPTION = _t, QUANTITY = _t, #"UNIT PRICE" = _t, #"TOTAL WITHOUT VAT" = _t, #"TOTAL WITH VAT" = _t, #"PURCHASE DELIVERY NOTE" = _t, VAT = _t, #"Product Type" = _t, RANGE = _t, #"Theoretical Points" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"INVOICE ID", type text}, {"CUSTOMER ID", Int64.Type}, {"PRODUCT ID", type text}, {"WAREHOUSE ID", Int64.Type}, {"DESCRIPTION", type text}, {"QUANTITY", Int64.Type}, {"UNIT PRICE", type text}, {"TOTAL WITHOUT VAT", Int64.Type}, {"TOTAL INCLUDING VAT", Int64.Type}, {"PURCHASE DELIVERY NOTE", type text}, {"VAT", Int64.Type}, {"Product type", type text}, {"RANGE", type text}, {"Theoretical points", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "ACTUAL POINTS", (k)=> Table.SelectRows(#"SCALED POINTS",each [From value]<=k[Target Points] and k[Target Points]< [Up to Value])[Points]{0},Int64.Type)
in
#"Added Custom"

POINT SCALING

Let
Source = Excel.Workbook(File.Contents("\\SERVAPP\Recreational Apps\Shopping Central\powerbi_backup\ESC. POINTS.xlsx"), null, true),
Hoja1_Sheet = Source{[item="Sheet1",kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
#"Type changed" = Table.TransformColumnTypes(#"Promoted Headers",{{"From Value", Int64.Type}, {"To Value", Int64.Type}, {"Points", Int64.Type}}),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxDcAwDMN+8ZzBFeC2vxj5/40iMrsQogZ2x52x4gV79ewrf54LU0F/Y5XQ39hT0J9NLoge5oLoYS6IHjaF09sf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"From value" = _t, #"To value" = _t, Points = _t]),
#"changed type" = table.transformColumnTypes(source,{{"from value", int64.type}, {"to value", int64.type}, {"points", int64.type}})
in
#"Changed Type"

What do I need to change for my data?

Thank you in advance

[lbendlin]

SALES INVOICE LINES

Let
Source = Sql.Database("192.168.15.30", "Dynamics365_BC130_CU03"),
#"dbo_GLOBAL FUN PART REAL$Sales Invoice Line" = Source{[Schema="dbo",Item="GLOBAL FUN PART REAL$Sales Invoice Line"]}[Data],
#"Other columns removed" = Table.SelectColumns(#"dbo_GLOBAL FUN PART REAL$Sales Invoice Line",{"Document No_", "Sell-to Customer No_", "No_", "Location Code", "Description", "Quantity", "Unit Price", "VAT _", "Line Discount _", "Line Discount Amount", "Amount", "Amount Including VAT", "Purch_ Document No_"}),
#"Renamed Columns" = Table.RenameColumns(#"Other Columns Removed",{{"Document No_", "INVOICE ID"}, {"Sell-to Customer No_", "CUSTOMER ID"}, {"No_", "PRODUCT ID"}, {"Location Code", "WAREHOUSE ID"}, {"Description", "DESCRIPTION"}, {"Quantity", "QUANTITY"}, {"Unit Price", "UNIT PRICE"}}),
#"Inserted Division" = Table.AddColumn(#"Renamed Columns", "Division", each [VAT _] / 100, type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Split",{{"Split", "VAT"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"INVOICE ID", "CUSTOMER ID", "PRODUCT ID", "WAREHOUSE ID", "DESCRIPTION", "QUANTITY", "UNIT PRICE", "VAT", "VAT _", "Line Discount _", "Line Discount Amount", "Amount", "Amount Including VAT", "Purch_ Document No_"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"VAT_", "Line Discount _", "Line Discount Amount"}),
#"Renamed Columns2" = Table.RenameColumns(#"Columns Removed",{{"Amount", "TOTAL WITHOUT VAT"}, {"Amount Including VAT", "TOTAL WITH VAT"}, {"Purch_ Document No_", "PURCHASE PACKING SLIP"}}),
#"Type Changed" = Table.TransformColumnTypes(#"Renamed Columns2",{{"CUSTOMER ID", Int64.Type}, {"WAREHOUSE ID", Int64.Type}, {"QUANTITY", Int64.Type}, {"UNIT PRICE", Currency.Type}, {"VAT", Percentage.Type}, {"TOTAL WITHOUT VAT", Currency.Type}, {"TOTAL INCLUDING VAT", Currency.Type}}),
#"Filtered rows" = Table.SelectRows(#"Type changed", each Text.StartsWith([PRODUCT ID], "A") or Text.StartsWith([PRODUCT ID], "R") or Text.StartsWith([PRODUCT ID], "M") or Text.StartsWith([PRODUCT ID], "O")),
#"Conditional Column Added" = Table.AddColumn(#"Filtered Rows", "Product Type", each if Text.StartsWith([PRODUCT ID], "A") then "GIFTS" else if Text.StartsWith([PRODUCT ID], "M") then "MACHINES" else if Text.StartsWith([PRODUCT ID], "R") then "SPARE PARTS" else if Text.StartsWith([PRODUCT ID], "OR") then "LEISURE CENTER SOLUTIONS" else if Text.StartsWith([PRODUCT ID], "C") then "BIRTHDAY" else if Text.StartsWith([PRODUCT ID], "B") then "BAR" else "ERROR"),
#"Added Custom" = Table.AddColumn(#"Conditional Column Added", "ACTUAL POINTS", (k)=> Table.SelectRows(#"POINT SCALING",each [From value]<=k[Target Points] and k[Target Points]< [Up to Value])[Points]{0},Int64.Type)
in
#"Added Custom"

POINT SCALING

Let
Source = Excel.Workbook(File.Contents("\\SERVAPP\Recreational Apps\Shopping Central\powerbi_backup\ESC. POINTS.xlsx"), null, true),
Hoja1_Sheet = Source{[item="Sheet1",kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
#"Type changed" = Table.TransformColumnTypes(#"Promoted Headers",{{"From Value", Int64.Type}, {"To Value", Int64.Type}, {"Points", Int64.Type}})
in
#"Type changed"

[Syndicate_Admin]

Good morning

When I apply the code you provide, I get the column I wanted, but it only shows me 5 lines. That is, this functionality limits the results to 5 lines, and I need all the ones in the table and the new ones that come in periodically.

It only shows me the following rows for the "SALES INVOICE LINES" table:

And in the STITCH SCALING table the same thing happens, only in this case it shows me 9 rows:

Can you please tell me how to make it stop you from deleting rows?

Thank you

[lbendlin]

I provided you the code based on your sample data. You need to swap in your own source.  

[Syndicate_Admin]

Good afternoon

Can you give me a solution to this question?

Thank you

[lbendlin]

Sales Invoice Lines:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZBNCsJADEavEroeYZI0085y8B/aCm1BUDyCFMQTeB5P5UnMFBW7tNjFJFlk3vfI8ZiQJZxZZM4Sk6ReS0Bi73Sw+tpwCNC0oYZ9qBtoN0soQ7UIxa7ehgqYBMpC95C0kPEpPG53HVkM5ZFIxvXQeR8jPsZYQ6htvV21jfbVpTsDxY9w7UBeBEKfnMxbkJAjDuVbEPnvhiSUjjaMmHxwQpxCUEYI0syKE37HfAT9fwU1xjlxYwVdLzi4YDaBIPNPgqcn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"INVOICE ID" = _t, #"CLIENT ID" = _t, #"PRODUCT ID" = _t, #"WAREHOUSE ID" = _t, DESCRIPTION = _t, QUANTITY = _t, #"UNIT PRICE" = _t, #"TOTAL EXCLUDING VAT" = _t, #"TOTAL INCLUDING VAT" = _t, #"ID DELIVERY NOTE PURCHASE" = _t, VAT = _t, #"Type of product" = _t, RANK = _t, #"Theoretical points" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INVOICE ID", type text}, {"CLIENT ID", Int64.Type}, {"PRODUCT ID", type text}, {"WAREHOUSE ID", Int64.Type}, {"DESCRIPTION", type text}, {"QUANTITY", Int64.Type}, {"UNIT PRICE", type text}, {"TOTAL EXCLUDING VAT", Int64.Type}, {"TOTAL INCLUDING VAT", Int64.Type}, {"ID DELIVERY NOTE PURCHASE", type text}, {"VAT", Int64.Type}, {"Type of product", type text}, {"RANK", type text}, {"Theoretical points", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Real Points", (k)=> Table.SelectRows(#"Escalation Points",each [From value]<=k[Theoretical points] and k[Theoretical points]< [Up to value])[Points]{0},Int64.Type)
in
    #"Added Custom"

Escalation Points:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYyxDcAwDMN+8ZzBFeC2vxj5/40iMrsQogZ2x52x4gV79ewrf54LU0F/Y5XQ39hT0J9NLoge5oLoYS6IHjaF09sf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"From value" = _t, #"Up to value" = _t, Points = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From value", Int64.Type}, {"Up to value", Int64.Type}, {"Points", Int64.Type}})
in
    #"Changed Type"

[Syndicate_Admin]

I have a column with certain values, and I would like to find in which range this value is located, that range is specified in another table. Finally, I want you to return the value determined for that interval.

I specify data below:

Values column to search:

Table --> Sales invoice lines

INVOICE ID	CLIENT ID	PRODUCT ID	WAREHOUSE ID	DESCRIPTION	QUANTITY	UNIT PRICE	TOTAL EXCLUDING VAT	TOTAL INCLUDING VAT	ID DELIVERY NOTE PURCHASE	VAT	Type of product	RANK	Theoretical points
2021-01337	49	A12396	0	TAZA STAR WARS THE MANDALORIAN 325 ML	12	2,94 €	35,28	42,69	AC21-01597	0,21	GIFTS	From 2 € to 5 €	219
2021-02138	15	A12396	13	TAZA STAR WARS THE MANDALORIAN 325 ML	12	2,94 €	35,28	42,69	AC21-02524	0,21	GIFTS	From 2 € to 5 €	219
2021-02139	89	A12396	1	TAZA STAR WARS THE MANDALORIAN 325 ML	12	2,94 €	35,28	42,69	AC21-02525	0,21	GIFTS	From 2 € to 5 €	219
2022-05653	12	A12396	9	TAZA STAR WARS THE MANDALORIAN 325 ML	12	2,94 €	35,28	42,69	AC22-06656	0,21	GIFTS	From 2 € to 5 €	219
2022-05663	19	A12396	7	TAZA STAR WARS THE MANDALORIAN 325 ML	12	2,94 €	35,28	42,69	AC22-06633	0,21	GIFTS	From 2 € to 5 €	219

Table with the intervals and the result to be returned:

Table --> Escalation Points

From value	Up to value	Points
60	80	80
80	100	100
100	125	125
125	150	150
150	175	175
175	200	200
200	225	225
225	250	250
250	275	275

Column 1 and 2 mark the interval, and column 3, the points to be returned

I would like to perform the query for each value in the "theoretical points" column of the "Sales invoices lines" table, and generate a column in the "Sales invoices lines" table for each value we consult.

For example, the expected result is as follows:

Theoretical Points	Real Points
219	225
219	225
219	225

I hope you can help me with this consultation,

Thanks and regards,

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...