Multiple Views combined into 1 Table/View using UNION (SQL)

I created a DB that combines data from multiple views into 1 view for me to pull into PowerBi. I use Power Query to append the views into 1 table for me to pull from and create my visuals. The problem is the DB is growing much larger then originally planned and my computer is have a very rough time doing that append now that we have grown from 48 to 300+.

My question is is there a way using SQL to combine these multiple DB View into one view prior to PowerBi?

Each DB (300+) has a single View created that is extracting the data needed to be combined. Each view contains all the exact same columns.

Any help would be appreciated. I have read into using “UNION ALL”, but not sure thats the correct route to go.

Answer

My question is is there a way using SQL to combine these multiple DB View into one view prior to PowerBi?

You can write a SQL Query like this

select 'View1' Source, a,b,c,d, . . . from View1
union all
select 'View2' Source, a,b,c,d, . . . from View2
union all
. . . 
union all
select 'ViewN' Source, a,b,c,d, . . . from ViewN

Note UNION ALL simply concatenates the results, whereas UNION deduplicates them. So always use UNION ALL if you want all the rows from all the views.

And then save that as an additional view, or call it through the Value.NativeQuery Power Query function.