Turn these temp tables into one longer subquery (can’t use Temp tables in Power BI)

Currently I have created these temp tables to get the desired output I need. However, Power BI doesn’t allow the use of temp tables so I need to get this all into 1 query using inner selects.

drop table if exists #RowNumber
Select Date, ID, ListID 
    , row_number() over (partition by ID order by ID) as rownum
into #RowNumber
from Table
where Date= cast(getdate()-1 as date)
group by Date, ID, ListID 
order by ID

drop table if exists #1stListIDs
select ListID as FirstID, ID, Date
into #1stListIDs
from #RowNumber
where rownum = 1

drop table if exists #2ndlistids
Select ListID as SecondListID, ID, Date
into #2ndlistids
from #RowNumber 
where rownum = 2

--Joins the Two Tables back together to allow the listids to be in the same row
drop table if exists #FinalTableWithTwoListIDs
select b.FirstListID, a.SecondListID, a.ID, a.Date
into #FinalTableWithTwoListIDs
from #2ndlistids a
join #1stListIDs b on a.ID= b.ID
order by ID

This code is simple and straight forward. However I can’t seem to figure out using a subquery. Here is what I have. It works for the FirstListID select statement, but not the SecondListID portion. I believe this is because you can’t reference the inner most select statement with multiple different outer select statements, but I could be wrong.

Select a.ListId as SecondListID, a.ID,  a.Date
from (


select a.ListId as FirstListID, a.ID,  a.Date
from (


Select Date, ID, ListId
    , row_number() over (partition by ID order by ID) as rownum
from Table
where Date = cast(getdate()-1 as date)
group by Date, ID, ListId
order by ID) a

where a.rownum = 1) b

where a.rownum = 2) c

Answer

Just to show, for completeness, how you could use CTEs to replace the #temp tables, it would be something along the lines of

with RowNumber as (
    select Date, ID, ListID 
    , row_number() over (partition by ID order by ID) as rownum
    from Table
    where Date= cast(dateadd(day,-1,getdate()) as date)
    group by Date, ID, ListID 
),
FirstListIDs as (
    select ListID as FirstID, ID, Date
    from RowNumber
    where rownum = 1
),
SecondListIDs as (
    select ListID as SecondID, ID, Date
    from RowNumber 
where rownum = 2
)

select f.FirstID, s.SecondID, s.ID, s.Date
from Secondlistids s
join FirstListIDs f on s.ID=f.ID
order by s.ID

Note the use of dateadd which is recommended over the ambiguousdate +/- value assumed to be days, and where relevant meaningful table aliases.