how to ignore null values of a column in Big Query (BQ)

Consider below approach

with temp_role1 as (
  select Project_Name, Role1, 
    countif(not Role1 is null) over(partition by Project_Name order by Role1) pos
  from your_table
), temp_role2 as (
  select Project_Name, Role2, 
    countif(not Role2 is null) over(partition by Project_Name order by Role2) pos
  from your_table
), temp_role3 as (
  select Project_Name, Role3, 
    countif(not Role3 is null) over(partition by Project_Name order by Role3) pos
  from your_table
)
select * except(pos)
from temp_role1
full outer join temp_role2 using (Project_Name, pos) 
full outer join temp_role3 using (Project_Name, pos) 
where format('%t', (Role1, Role2, Role3)) != '(NULL, NULL, NULL)'
# order by Project_Name, pos 

If apply to sample data in your question - output is

This solution should at least give you an idea to complete your case with all 15+ roles. You can either just repeat/add extra CTEs(temp_roleN) and same amount of JOINS without any extra changes OR you can try to generalize above code and make it more generic - for example using some scripting with EXECUTE IMMEDIATE, etc.

Consider also below approach - it is very easily extended to any numbers of roles - just add them in respective lists: (Role1, Role2, Role3) and ('Role1', 'Role2', 'Role3')

select * except(pos)
from (
  select *, row_number() over(partition by Project_Name, role order by user) pos
  from your_table
  unpivot (user for role in (Role1, Role2, Role3))
)
pivot (any_value(user) for role in ('Role1', 'Role2', 'Role3'))    

if applied to sample data in your question - output is