Creating multiple tables / queries using 1 Power Query script

[pbizombie]

Hi there, 

We have multiple tables we'd like to create dynamically using power query scripts, but ideally we'd like all these tables / queries to be created with one script, not one script per query.  

Is there any way to do this using Power Query or some other scripting mechanism? 

Thanks in advance. 

[danextian]

Hi @pbizombie 

Do you mean querying different data tables of similar structure dynamically? The logic will depend on the data source types. Please elaborate.

[pbizombie]

Hi @danextian 

So we've got about 9 queries in our query list that we make viewable to the client via our embedded app.  

If I open one of the queries in the advanced editor I get the power query code for that query that I can copy and paste in a new dataset to create that same table.  

Is there a way to combine the power query code of all 9 queries into a single power query script so all 9 queries are created?  Or is it strictly 1 query created per power query?  

If so do you know of any other ways to quickly script in a bunch of queries to a dataset? 

Hope that makes sense. 

Cheers 

[danextian]

Hi @pbizombie 
Here is sample code of multiple queries combined into one using Table.Combine without having write a separate query for each. Take note that each subquery (A and B) must return a table data type otherwise you won't be able to combine them. 

let
 QueryA = 
    let
        Source = Accounts_main,
        #"Uppercased Text" = Table.TransformColumns(Source,{{"Type", Text.Upper, type text}}),
        #"Filtered Rows" = Table.SelectRows(#"Uppercased Text", each ([Type] = "BANK")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Account ID", "Code"})
    in
        #"Removed Other Columns",
 QueryB = 
    let
        Source = SharePoint,
        #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "Unit Budget.xlsx"),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
        Content = #"Removed Other Columns"{0}[Content],
        #"Imported Excel" = Excel.Workbook(Content),
        #"Filtered Rows1" = Table.SelectRows(#"Imported Excel", each [Kind] = "Table"),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Unpivoted", each Table.UnpivotOtherColumns([Data], {"Column1"}, "MonthYr", "Value"), type table),
        #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Item", "Unpivoted"}),
        #"Expanded Unpivoted" = Table.ExpandTableColumn(#"Removed Other Columns1", "Unpivoted", {"Column1", "MonthYr", "Value"}, {"Column1", "MonthYr", "Value"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Unpivoted",{{"Value", type number}, {"MonthYr", type date}, {"Column1", type text}}),
        #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Category", each Text.BeforeDelimiter([Item], "FY"), type text),
        #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Item"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Item"}}),
        #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Calendar Month and Year", 
                            each let
                                _mo = Text.Start(Date.MonthName([MonthYr]),3),
                                _yr = Text.From(Date.Year([MonthYr]))
                            in _mo & " " & _yr, type text),
         #"Renamed Columns1" = Table.RenameColumns(#"Added Custom1",{{"Value", "Monthly Unit Budget"}}),
        #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns1", "CalMoYr_Category", each Text.Combine({[Calendar Month and Year], [Category]}, "_"), type text),
        #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"MonthYr", "CalMoYr_Category"}),
        #"Filtered Rows2" = Table.SelectRows(#"Removed Columns1", each [Item] <> null and [Item] <> "")
    in
        #"Filtered Rows2"
in Table.Combine({BA, Unit})

 I would prefer to separate them then just combine them together using Append as it will be easier to modify them this way. 

Here's another way to combine different tables of the same data structure that reside in one or more sql databases using Table.ExpandTableColumn.

let
    Source = Sql.Database("database.loc", DatabaseID, [CommandTimeout=#duration(0, 5, 0, 0)]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Schema] = "dbo"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Kind] = "Table"),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"accountid", "bankaccountnumber", "bankaccounttype", "blendo_imported_at"}, {"accountid", "bankaccountnumber", "bankaccounttype", "blendo_imported_at"})
in
    #"Expanded Data"