Power Query greater or less than in relationship definition

0
votes

I have an employee table, and want to count employees on each group of Salaries.

So I created another table showing groups and joined them like this in SQLite3: (which worked);

    SELECT s.SalaryGroupName SalaryGroup,
       Count(e.ID) HeadCount
  FROM Emp e
       JOIN
       SalaryGroup s ON s.MiniSalary <= e.Salary AND 
                        s.MaxSalary >= e.Salary                      
GROUP BY s.SalaryGroupName;

but in Power Query (Excel 2016) I could not merge these two tables in this way through wizards. (I do not know M language). Is there any way to do that in Power Query. (simplest way possible)??!

1
powerquerym
Can you give an example data table along with the results you want? – Alexis Olson
the final resual will be a table like this : Salary between 2000-3000 = 890 persons , Salary between 3001 to 4000 = 678 persons, and so on. The original tables are Emp (Employee table with columns ID, Name, Age and Salary) and another table shows the salary ranges with columns (ID, RangeName, MinSalary, MaxSalary) – Leo Sam
Please edit that into the post. – Alexis Olson

1 Answers

0
votes

What about multiply tables, expand and filter?

let
    src1 = #table({"k1", "v1"}, {{"k11", 11}, {"k12", 12}, {"k13", 13}, {"k14", 14}}),
    src2 = #table({"k2", "v2"}, {{"k21", 21}, {"k22", 22}, {"k23", 23}, {"k24", 24}, {"k25", 25}}),
    custom = Table.AddColumn(src1, "custom", each src2),
    expand = Table.ExpandTableColumn(custom, "custom", {"k2", "v2"}, {"k2", "v2"}),
    filter = Table.SelectRows(expand, each ([v1] <= 13) and ([v2] >= 22))
in
    filter

src1 enter image description here

src2 enter image description here

add custom column enter image description here

expand enter image description here

filter your conditions enter image description here

FYI - Expression.Error: Local evaluation of Table.Join or Table.NestedJoin with key equality comparers is not suported