Forum Discussion

Concept's avatar
Concept
New Member
1 year ago

Populating ODATA query from a data table

Hi Everyone,

 

I have an ODATA feed which pulls data back to PowerBI and is functioning.

 

It has been created using a blank query, where the number highlighted in blue is the data point reference

 

let
      Source = OData.Feed("https://odata.c3ntinel.com/v0.1/", null, [Implementation="2.0"]),
      SelectFunction = Source{[Name="GetCustomDatesMeterReadings",Signature="function (meterId as number, resolution as text, start_date as datetimezone, end_date as datetimezone) as nullable record"]}[Data],
     GetCustomDatesMeterReadings = SelectFunction(287915, "Month", #datetimezone(2022, 1, 1, 0, 0, 0, 0, 0), #datetimezone(2025, 01, 31, 0, 0, 0, 0, 0)),
     Readings = GetCustomDatesMeterReadings[Readings]

in
     Readings

 

I have a large number of data point references that I need to pull. 

I have already imported the list of reference numbers as a data table.

 

Is there any way of dymanically changing the reference number in the ODATA call so that I don't have to either hard code the refernce numbers or create 50+ query tables?

 

Many thanks  

Need Help

1 Reply

  • smpa01's avatar
    smpa01
    Community Champion
    1 year ago

    Concept  yes, it is doable in theory but performance will take a huge hit and it is not scalable.

     

    Pseudocode

     

    // write a function get_data with table_num parameter
// pseudocode
(table_num as integer)=>
let
      Source = OData.Feed("https://odata.c3ntinel.com/v0.1/", null, [Implementation="2.0"]),
      SelectFunction = Source{[Name="GetCustomDatesMeterReadings",Signature="function (meterId as number, resolution as text, start_date as datetimezone, end_date as datetimezone) as nullable record"]}[Data],
     GetCustomDatesMeterReadings = SelectFunction(table_num, "Month", #datetimezone(2022, 1, 1, 0, 0, 0, 0, 0), #datetimezone(2025, 01, 31, 0, 0, 0, 0, 0)),
     Readings = GetCustomDatesMeterReadings[Readings]

in
     Readings


//table_num list
table_num = [1,2,3,....n]

//forEach in table_num invoke get_data
List.Geneerate() - POWER Query For loop

     

     

    Better alternative (if you have fabric), use notebook to perform this task.