Excel OData: Multiple Queries from Multiple Source...

Anonymous · ‎01-09-2023

Hello,

I was hoping someone might be able to tell me if it is possible to build a set of queries, loaded to tables in Excel, from a list of sources. I;'ve worked with single parameters before (like pasing a DATE into a query) but never iterating through a 'list' of parameters.

With my limited knowledge, I understand that a standard query has a source, formed somethign like this;

Source = OData.Feed("SOURCE_ENETERED_HERE", null, [Implementation="2.0"]),
THIS_IS_MY_DATA_table = Source{[Name="THIS_IS_MY_DATA",Signature="table"]}[Data],

What I would like to do is load a 'list' of data source names into a table in Power Query and the generate the queries from these, I currently create these manually, somethign like;

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_1_table = Source{[Name='THIS_IS_MY_DATA_1',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_2_table = Source{[Name='THIS_IS_MY_DATA_2',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_3_table = Source{[Name='THIS_IS_MY_DATA_3',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_4_table = Source{[Name='THIS_IS_MY_DATA_4',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_5_table = Source{[Name='THIS_IS_MY_DATA_5',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_6_table = Source{[Name='THIS_IS_MY_DATA_6',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_7_table = Source{[Name='THIS_IS_MY_DATA_7',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_8_table = Source{[Name='THIS_IS_MY_DATA_8',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_9_table = Source{[Name='THIS_IS_MY_DATA_9',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_10_table = Source{[Name='THIS_IS_MY_DATA_10',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_11_table = Source{[Name='THIS_IS_MY_DATA_11',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_12_table = Source{[Name='THIS_IS_MY_DATA_12',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_13_table = Source{[Name='THIS_IS_MY_DATA_13',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_14_table = Source{[Name='THIS_IS_MY_DATA_14',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_15_table = Source{[Name='THIS_IS_MY_DATA_15',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_16_table = Source{[Name='THIS_IS_MY_DATA_16',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_17_table = Source{[Name='THIS_IS_MY_DATA_17',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_18_table = Source{[Name='THIS_IS_MY_DATA_18',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_19_table = Source{[Name='THIS_IS_MY_DATA_19',Signature='table']}[Data],

Source = OData.Feed('SOURCE_ENETERED_HERE', null, [Implementation='2.0']), THIS_IS_MY_DATA_20_table = Source{[Name='THIS_IS_MY_DATA_20',Signature='table']}[Data],

What would be great, would be to store the following in a table in power query and then have power query 'iterate' though the list and create the 20 queries (and tables) in excel for me, is this possible?

LIST OF DATA SOURCES

THIS_IS_MY_DATA_1

THIS_IS_MY_DATA_2

THIS_IS_MY_DATA_3

THIS_IS_MY_DATA_4

THIS_IS_MY_DATA_5

THIS_IS_MY_DATA_6

THIS_IS_MY_DATA_7

THIS_IS_MY_DATA_8

THIS_IS_MY_DATA_9

THIS_IS_MY_DATA_10

THIS_IS_MY_DATA_11

THIS_IS_MY_DATA_12

THIS_IS_MY_DATA_13

THIS_IS_MY_DATA_14

THIS_IS_MY_DATA_15

THIS_IS_MY_DATA_16

THIS_IS_MY_DATA_17

THIS_IS_MY_DATA_18

THIS_IS_MY_DATA_19

THIS_IS_MY_DATA_20

BA_Pete · ‎01-10-2023

Hi @Anonymous ,

Technically it's possible. You would essentially just replace the references in the Source/Navigation steps to a position in your list.

So, references to 'THIS_IS_MY_DATA_1' would become 'LIST OF DATA SOURCES'{0}.

There will be other intricacies to getting this to work exactly as required (for example, error handling queries that reference a list position beyond the length of your source list etc.), but this is the principle of the method.

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!