Create multiple tables from single query

[chriswragge]

Hi there,

I have 20 differing queries all essentially referencing the same basic code.

= #"Base Code - New"(startDateNew, endDateNew, "at")

"Base Code - New" is a function that takes in 3 vars. startDateNew and endDateNew are parameters, and the last var is a country code that changes. This means that I have 20 queries, all with a different 3rd var.

e.g.

= #"Base Code - New"(startDateNew, endDateNew, "be")

= #"Base Code - New"(startDateNew, endDateNew, "ca")

and so on...

What I would like to do is combine these 20 queries into a single query in order to simplifiy my code. Is this possible?

Note - All queries produce the same column headers and data types

Bonus points: Even though this post title doesn't explicitely state it, an even better solution (my next question) would be how to union all of these tables into a single table through the query editor. This way I would would have one query producing one table from 20 different sources via a function.

Thanks!

[MFelix]

Hi @chriswragge,

You can do this by creating a funciton and running the same set of instructions agains the same query check the link to see how to setup a custom function.

If you are talking about different files, you can also use the get data from folder and then choose the edit and combine option this will create the custom function for you and allow you to make changes and adapt to your needs. Check this link although is for Power Query in Excel it's the same intrstucitons as in Power BI.

In this case the variable for use on the custom function can be a table with StarDate, EndDate and "at" you just need to combine them in a single parameter code.

Not having any information regarding the type of data you have is difficult to help you better on how to achieve this.

But making this with the custom function and using a table with all your options for the 3 variables it will give you a single append querie on your model.

Regards,

Mfelix

[chriswragge]

Thanks for your help here @MFelix. I already am leveraging a function (Base Code - New), and am passing in 3 parameters to this. However my issue lies in that I have 20 sources for this function (differing Adobe Analytics report suites accessed via the API). 

Currently what I am doing is running 20 queries, each with a different source.

What I would like to do is have 1 query, and loop through my function 20 times, each time adjusting my 'country' parameter (startDateNew and endDateNew remain the same). This would clean up my queries.

Currently the 20 queries create 20 tables and then I have used a little DAX to UNION them all together in a new table. 

My ideal output would be a single table with all of the data appended. This would clean up my tables. 

[MFelix · Accepted solution]

Hi @chriswragge,

As it is refered in the links I provided you can do a single query that is transformed in a function on that function you need to refer to a variable (identified as (something) => )

Then adding a table with all the variable you need to change alonside with the custom functions you will get a single querie and not 20.

See attach an example with a webpage change.

Custom Function

(page) =>

let
    Source = Web.Page(Web.Contents("https://editorial.rottentomatoes.com/guide/200-essential-movies-to-watch-now/" & Number.ToText(page) &"/")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", Percentage.Type}, {"Column2", type text}})
in
    #"Changed Type"

 Final Query

let
    Source = {2..5},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each WebPage([Column1])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Custom.Column1", "Custom.Column2"})
in
    #"Expanded Custom"

Regards,

MFelix

[chriswragge]

This is so cool. This was exactly what I was looking for!! Thanks so much for all the help @MFelix.

[chriswragge]

And again, thanks for the help here and apologizes for not being clearer before 🙂