Relate data from one table to ranges from another

Here's my approach:

 

You can do the list conversion in either table. For no good reason I did it only in the Characters table.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUTKzUIrViVY6vBbINjI2B3PygGxDQwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Character = _t, Unicode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unicode", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Lookup",  (k)=>
Table.SelectRows(Unicode,
each (List.Contains(Expression.Evaluate("{" & Text.Replace([Decimal],"-","..") & "}"),k[Unicode]) = true ))),
    #"Expanded Name" = Table.ExpandTableColumn(#"Added Custom", "Lookup", {"Name"}, {"Name"})
in
    #"Expanded Name"

 

 

A couple comments.

Note that I changed the Unicode column of the Characters table to Whole number.

I am ignoring the Character column of the Characters table.

The interesting part happens in the AddColumn line. I employ a custom generator function that pulls in the current row context ("k") .

Then I concatenate the information from the Unicode table in a way that it represents the text of a list definition.

Next I EVAL that expression, converting it into a list. 

The next step is to return all rows of the Unicode table where the Unicode value of the Characters table ( "k[Unicode]" ) is contained in the generated list.  Luckily this always returns maximum one row 🙂

Lastly I expand the returned row and retrieve the lookup name of the Unicode range.

Note there is no "each"  in front of the "(k)=>"  function definition. This is intentional! When you edit this part Power Query will re-insert the "each" keyword, breaking the code. You will need to manually remove it again.

 

Here's the result:

 

 

Hello carlpaul153 

 

you can Manipulate your Unicode table in order that for the decimal range columns you have 2 different columns with type number. Here the code for the query called "Unicode"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqgwAAJdEGXupqSjZKBraGQOpJ0SizOTFXwSSzLzlGJ1IOosIOrcQOoMjSx0jUxNgSywGl1DheDSgoKc1NzUvBKYBkOIwYZgg41MzXSNLYxhGhRcK0pS81JSU3QdlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Range = _t, Decimal = _t, Type = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Decimal", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Decimal.1", "Decimal.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Range", type text}, {"Decimal.1", Int64.Type}, {"Decimal.2", Int64.Type}, {"Type", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Decimal.1", "Start"}, {"Decimal.2", "End"}})
in
    #"Renamed Columns"

 

Now use your character table and add a column where you filter the unicode table on decimal ranges. The formula here does the trick.  ..  important is that the unicode-table is called "Unicode"

(add)=>
        let 
            FilterTable = Table.SelectRows(Unicode, each add[Unicode] >= [Start]  and  add[Unicode]<=[End]),
            ExtractType = try FilterTable[Type]{0} otherwise "Unicode not defined in Unicode table"
        in 
            ExtractType

 

here the complete code for the characters-table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUTKzUIrViVY6vBbINjI2B3PygGxDQwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Character = _t, Unicode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Character", type text}, {"Unicode", Int64.Type}}),
    AddType = Table.AddColumn(#"Changed Type", "Type", (add)=>
        let 
            FilterTable = Table.SelectRows(Unicode, each add[Unicode] >= [Start]  and  add[Unicode]<=[End]),
            ExtractType = try FilterTable[Type]{0} otherwise "Unicode not defined in Unicode table"
        in 
            ExtractType)
in
    AddType

  Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy