Need help creating a Power query on alphabetical range

[Richard U]

I know I'm missing something really simple:

How can I do a query where I can return records based on customer names. In other words, something that would return all customer names staring with 1 to j, or K to P or N to Z

 

[JGordon11]

M code in CustomerData Query (relies on FromTo table loaded as connection only):

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Character.ToNumber([Name])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] >= Character.ToNumber(FromTo{0}[From]) then true else false),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.2", each if [Custom] <= Character.ToNumber(FromTo{0}[To]) then true else false),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom.1] = true) and ([Custom.2] = true)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"})
in
    #"Removed Columns1"

 

[Richard U]

I'm very new to PQ, I see the results, but I don't understand what the M code is doing, or how I would modify it for my purposes. Could you please explain a bit?

 

[JGordon11]

Source = Excel.CurrentWorkbook(){[Name="[/COLOR][COLOR=rgb(44, 130, 201)][B]CustomerData[/B][/COLOR][COLOR=rgb(226, 80, 65)]"]}[Content],
takes the data from the current workbook from a table named CustomerData (change to your table name) and assigns it a name Source

#"Added Custom" = Table.AddColumn(Source, "Custom", each Character.ToNumber([Name])),
adds a column to the Source data table containing the ascii code for the first character in each cell in column Name (change Name to the appropriate column name in your table)
and calls the new column Custom and the resulting table #"Added Custom"

#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] >= Character.ToNumber(FromTo{0}[From]) then true else false),
Adds a column to the #"Added Custom" table based on a condition that the ascii value in Custom is greater than or equal to the From number from table FromTo (with columns From and To) that has been loaded as a connection only (Change FromTo to whatever you named your FromTo table) if condition is true it puts true in the new column called Custom.1 otherwise false

#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.2", each if [Custom] <= Character.ToNumber(FromTo{0}[To]) then true else false),
Similar to the above step but true if less than or equal to the To value in Table FromTo

#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Custom"}),
Remove the Custom column from step 2 since we don't need it

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom.1] = true) and ([Custom.2] = true)),
Filers the table to only those rows that have true in Custom.1 AND Custom.2

#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"})
Remove Custom.1 and Custom.2 because we no longer need them

So this query relies on two tables in Excel. In my example the large table is called CustomerData and the small Table is called FromTo. Both have to be inputted into power query for it to work. The text in bold blue needs to be changed to match your table names and Column names.