cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

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

 

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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
Administrator
Administrator

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

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

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?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (3,433)