Forum Discussion
Transfer Query result in data table. Update or Create new row based on the primary key
Hi louagej,
Maybe you can try to use 'Table.InsertRows' function to add custom row.
Regards,
Xiaoxin Sheng
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
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
Dear Xiaoxin Sheng,
Maybe my explanation was'nt very clear but I don't want to add an extra column.
I just need to insert a new row in case the primary key is new or update the row when it already exist.
All colums are already in the table.I read on Power BI forum that it might be better to use direct query when we're dealing with large datasets.
Maybe I'm trying to use Power BI for something that it wasn't designed for.Kind regards,
JobHi louagej,
Maybe you can try to use 'Table.InsertRows' function to add custom row.
Regards,
Xiaoxin Sheng