power query loop from sql dynamic table name

Question

i have an MS SQL running under Power Query to get some data into Excel.

we have several projects and codes for them which comes from another query as follows :

let
    Source = Projects[Code]
in
    Source

Sample data :

+------+
| code |
+------+
|    2 |
|    3 |
|    6 |
|   76 |
|   77 |
|   78 |
|   79 |
|   80 |
|   81 |
|   82 |
|   83 |
|   84 |
|   85 |
|   86 |
|   87 |
|   88 |
|   89 |
|   90 |
|   91 |
|   92 |
|   93 |
|   94 |
|   95 |
|   96 |
|   97 |
|   99 |
|  100 |
|  101 |
|  102 |
|  103 |
+------+

what i want to do is, to run the below main query for each project code.

let
    Source = Sql.Database("server", "logo", [Query="select '92' as [santiye] , stockref, DATE_ , AMOUNT, PRICE, total from LG_092_01_STLINE where cancelled = 0 and TRCODE =1"]),
    #"Merged Queries" = Table.NestedJoin(Source,{"stockref"},Query2,{"stockref"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"CODE", "NAME"}, {"NewColumn.CODE", "NewColumn.NAME"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"stockref"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"NewColumn.CODE", "NewColumn.NAME", "DATE_", "AMOUNT", "PRICE", "total"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"santiye", Int64.Type}}),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type",{"santiye"},Projects,{"Code"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Project Name"}, {"NewColumn.Project Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn1",{{"NewColumn.Project Name", "Project Name"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"santiye", "Project Name", "NewColumn.CODE", "NewColumn.NAME", "DATE_", "AMOUNT", "PRICE", "total"})
in
    #"Reordered Columns1"

the name of the table in SQL shall be changed dynamically and taken from the other query as a loop and union all data at once.

LG_092_01_STLINE = LG_{code}_01_STLINE

[Query="select '92' as = [Query="select {code} as

i made similar loop in SQL query but "exec()" does not work in power query unfort.

while @cnt <= @maxrow

begin 

select @firma = nr from (SELECT ROW_NUMBER() over (order by logicalref asc) as NUM, NR FROM L_CAPIFIRM  ) nn where NUM = @cnt   
select @santiye = name from L_CAPIFIRM where NR = @firma

set @firma = RIGHT('000'+CAST(@firma AS VARCHAR(3)),3)
set @items = 'LG_' + convert(nvarchar(3),@firma) + '_ITEMS'
set @stline = 'LG_' + convert(nvarchar(3),@firma) + '_01_STLINE'

Answer 1

I think it's quite straightforward to do this without writing a line of M code, given that it seems your list of columns in each table is consistent.

First I would connect to a SQL database on that server and choose any random table and Edit the generated query. Then navigate back to the Source step and click on the row for your Database, in the Data column. This will give you a list of tables in that Database.

Filter the list to the tables you want. You can do this with any kind of Filter, or for your scenario you could achieve that by a Merge using the Join Kind: Inner Join.

Now hit the Expand button in the column heading for the Data column. You now have every row from the selected tables, appended together. You can add in your downstream transformations to this query.

Done in 4 clicks without a line of code. Power Query is an amazingly powerful data integration tool.

Power Query has an astonishing tolerance for variations in the schema of the tables - I've never seen anything like it. It generally ploughs on with common-sense results like returning null for tables with missing columns, matching columns by name (not column position as in a SQL UNION), and attempting to cast columns with conflicting datatypes. It appears to generate the column list from the first table. As always, test carefully.