Loop Through Table using Table Insert Rows in Power Query -Power BI

[Syndicate_Admin]

Hi Team, We are trying to insert the data comming from Web Service into Another table with help of Table.InsertRows and data is dynamic. What will be the query for Table.insertrows?
Data comming from service:  #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.participant",{{"Column1.participant.firstName", "firstName"}, {"Column1.participant.lastName", "lastName"}, {"Column1.participant.email", "email"}, {"Column1.participant.referenceId", "referenceId"}, {"Column1.participant.location", "location"}, {"Column1.participant.status", "status"}, {"Column1.testResult.testId", "testId"}, {"Column1.testResult.result", "result"}, {"Column1.testResult.resultDate", "resultDate"}, {"Column1.testResult.testType", "testType"}, {"Column1.testResult.testChannel", "testChannel"}}),

Insert all these data into Another table in same PowerBI report. Can we loop the Table.insertrows step and which will dynamically take values from above Renamed Columns step and add into another table. 
Loops in Power Query M language (potyarkin.ml) this is basic syntax for Loop in PowerQuery
Table.InsertRows - PowerQuery M | Microsoft Docs this is basic syntax for insert rows

Can someone please help to get this resolved?  We actually need to insert the rows from Web service to another table on each click on "Refresh"  action so that we can have a history table

 

[Syndicate_Admin]

Hi @Akshay_Nis_014 ,

You're not going to be able to maintain a history table in this way with Power Query.

The Power Query refresh process is: Wipe everything > Load current status from source > perform transformation steps.

As you can see, the 'wipe everything' step will also wipe any previous values you had in your 'history' table.

If you want to add snapshots to a growing history set at each refresh, I would recommend either setting this up within your data warehouse, or using Incremental Refresh in the PBI Service:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure 

Pete

[Syndicate_Admin]

I have did Increamenal Refresh for Table having data populating from Web Service. But seems like according to post  https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview  Folded M Query is not supported datasource comming from Web Service and ultimately Increamental Refresh wont work

And Also, Collected Data from Web Service API into PBI Table-->Set up Increamental Refresh-->Saved and Publish the report-->Scheduled the Refresh-->Manually Clicked on Refreshed but records are not updated and increased
like previously no. of rows was 3 and after refresh it is still 3 
but it should be 6 
because initialy it was 3 --> then i can see on Web there are 3 records are present-->

clicked on refresh, it should add 3 to existing 3 so total should be 6 but it is 3

[Syndicate_Admin]

That's a shame if your web source isn't foldable. I can only suggest that you have this function built into your data warehouse ETL process, if possible, as Power Query isn't going to help you in a production environment.

There are a few hacks that people have used that are available on these forums, but I'm not going to link to them as they are hacks, and not suitable for a production environment, but they are available if you wanted to pursue this course.

Pete

[Syndicate_Admin]

So was just thinking to write the R-Scrip which will export the data into CSV and then collect that CSV into PowerBI and create Increamental refresh on that file. Will it work? 
Can we assure that on Excel File incremental refresh will work. Even i have created sample excel workbook and created PBI report and set up incremental refresh on it and published, clicked on refresh, it just refreshed, then made changes in Excel sheet removed records, saved file, added some records, but the count of rows was same. incremental refresh has not preserved previous records, can you please help me with this?