cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohankvs
Frequent Visitor

Excel WorkBook's Power Query Connections doesn't work after connection with PowerApps

Hi All,

 

I have an excel file with power query connection. I connected this to Power App. Now, connection to power query is lost though query exists. Data is no longer getting loaded. 

 

In one of the old case rasied in 2018, i see that there was no solution. Since we are in 2020, wanted to check if there is any update to this challange or workaround?

 

Thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @mohankvs :

I'm no sure if I understand what you mean correctly,please help me pooint it out if there is an error:

  • In the excel file, you get data through Power Query.
    You use this excel file as your data source.
  • Currently you can get the correct data structure, but you cannot get any record.

Firstly,let me explain why you encountered this problem.

PowerApps assigns a unique identifier to each row by adding a column called __PowerAppsId__.If the table is created by Power Query, "__PowerAppsId__" will not be added automatically.Because of the lack of a unique identifier, the data will not be returned to powerapp.

Secondly,manually inserting a field named "__PowerAppsId__" can solve this problem, but it may affect the performance of Power Query connection and cause it to fail to refresh.

1.JPG

1. Manually insert a field named "__PowerAppsId__" in the table
2. Delete and recreate the connection in PowerApps
3. You can get the records in the table (the value in the __PowerAppsId__ field in the Table will be filled in automatically, you can ignore it)

In addition,although this method can solve the problem, I still recommend that you use CDS as a data source. CDS also supports Power Query and is much more stable than Excel.

I think this link will help you a lot:

Load data from external data sources to the Common Data Service using Power Query (Previous Version) 

Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

Hi @mohankvs :

I'm no sure if I understand what you mean correctly,please help me pooint it out if there is an error:

  • In the excel file, you get data through Power Query.
    You use this excel file as your data source.
  • Currently you can get the correct data structure, but you cannot get any record.

Firstly,let me explain why you encountered this problem.

PowerApps assigns a unique identifier to each row by adding a column called __PowerAppsId__.If the table is created by Power Query, "__PowerAppsId__" will not be added automatically.Because of the lack of a unique identifier, the data will not be returned to powerapp.

Secondly,manually inserting a field named "__PowerAppsId__" can solve this problem, but it may affect the performance of Power Query connection and cause it to fail to refresh.

1.JPG

1. Manually insert a field named "__PowerAppsId__" in the table
2. Delete and recreate the connection in PowerApps
3. You can get the records in the table (the value in the __PowerAppsId__ field in the Table will be filled in automatically, you can ignore it)

In addition,although this method can solve the problem, I still recommend that you use CDS as a data source. CDS also supports Power Query and is much more stable than Excel.

I think this link will help you a lot:

Load data from external data sources to the Common Data Service using Power Query (Previous Version) 

Best Regards,

Bof

Table is created by Power Query, and "__PowerAppsId__" is added automatically. But, connection to query is lost. Also, in one of the article i read that we can't connect both PowerApps and power query at the same time and may be that is wish list. I have used SharePoint list to import data. Thanks for your help. 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,703)