Forum Discussion

louagej's avatar
louagej
Regular Visitor
8 years ago
Solved

Transfer Query result in data table. Update or Create new row based on the primary key

Hi,

I'm using a odata service to load the latest changes from a table based on the SQL Timestamp.

We want to avoid that the query is loading each night millions of records.

So the result of my Query 1  can be a new or changed record and only returns a couple of hunded records each night.

 

After I this query is executed, I want to copy the rows to a data table in the report.

Based on the primary key, the second query should create a new row or update an existing row.

 

Any dea if that's possible?

Kind regards,

Job

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi louagej,

     

    Maybe you can try to use 'Table.InsertRows' function to add custom row.

    Table.InsertRows

     

    Regards,

    Xiaoxin Sheng

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi louagej,

     

    >>So the result of my Query 1  can be a new or changed record and only returns a couple of hunded records each night.

    You can try to use url convert to apply filter on result query.

    URL Conventions (OData Version 3.0)

     

    >>After I this query is executed, I want to copy the rows to a data table in the report.Based on the primary key, the second query should create a new row or update an existing row.

    It is possible to create new row to lookup the update data, this created new column/row is a virtual column/row. Every time update/refreshed, these data will lost.

    Good Ol' VLOOKUP - The Ultimate Guide to Lookups in Power BI

     

    Regards,

    Xiaoxin Sheng

    • louagej's avatar
      louagej
      Regular Visitor
      8 years ago

      hi Xiaoxin Sheng,

       

      I'm already using the filtering on my oData set to only receive the changed or new records.

      "http://<Servername>:<Serverport>/<Serverinstance>/OData/Company('CRONUS%20BELGI%C3%8B%20NV')/Items?$filter=SQL_Timestamp gt " & Number.ToText(Item_SQL_Timestamp).

       

      The intention is that I can store this data in the report itself, so that it doesn't need to be loadthe complete dataset with every refresh.

       

      My next SQL Timestamp for the next refresh would be the one I receive from my previous call:

       

       

      Kind Regards,

      Job

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI louagej,

         

        >>The intention is that I can store this data in the report itself, so that it doesn't need to be loadthe complete dataset with every refresh.

        Yes, it is possible to look up value from another table and create a custom column to store them. For this column, it will be changed every time you refresh datasource.

         

        Formula:

         

        #"Added Custom" = Table.AddColumn(#"previous steps", "ColumnName", each Function.Invoke((id as any) => Table.SelectRows(#"QueryName",each [SearchColumn]= id),{[ParameterColumn]})[ResultColumn])

         

        Sample:

        #"Added Custom" = Table.AddColumn(#"previous steps", "SQL_TimeStamp", each Function.Invoke((No as any) => Table.SelectRows(Table2,each [No]= No),{[No]})[TimeStamp])

         

        Regards,

        Xiaoxin Sheng