Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sudhavi_84
Helper V
Helper V

How to Summarise Sharepoint list please to bring into Power App?

Hello Experts, 

 

I have a Budget and Sales data in excel format.

Budget file includes duplicate stores (based on criteria) and values .

Sales data includes duplicate stores (based on criteria) and values .

 

What my requirement is, I need to create a Power App, so that users need to have a visible of Budget and Actual sales side by side and they put some values/comments based on the variance/comments to it and it should update in report.

 

So what I am doing here is I converted it to List and I got an idea to create in Power App with the help of @amitchandak  link https://community.powerbi.com/t5/Power-Platform-Integrations/Power-Apps-Power-BI-Better-together-Wri...

 

So my question is, to compare Budget and Sales together and to allow users to update the variance/comments, 

 

First I have to summarise the Budget and Sales data in List isn't it? like I need to remove duplicates and sum the values so that store number won't repeat twice.

How can I automate this list on monthly basis? Do I need to use power Automate first to summarise in list and then should I create Power App after that?

 

If I need to do with Power Automate with flow I am completely new, could any one please shed some lights on me on going forward?

 

Thank you

 

Thank you

 

 

 

1 ACCEPTED SOLUTION

@Sudhavi_84 

 

As long as these columns exist, you can create a measure to generate the last column. But I am only familiar with power bi, so it may be difficult to understand what you said in other aspects.

vjaneygmsft_0-1625212393178.png

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Sudhavi_84
Helper V
Helper V

@v-janeyg-msft ,

 

I am sorry, may be I missed additional steps on this.

 

Yes I agree that we can do this in Power BI Table visual.

 

Current requirement is users need to enter the spend values in additional columns manually. They will enter manually which city how much they can spend etc..  We have static additional city columns, End users will enter foreast manually in to the City columns.

 

 

Sudhavi_84_0-1625211855158.png

 

 

 

 

And there will be Variance column to check the difference between Total Values and Sum(City's)

 

Currently from Column G to I, I agree that Power BI can do, but from Column J to Column O it can be done only using Power APP I think.

 

Users should able to enter the values and it should get updated in reports after submitting button in Power App.

 

So my question is

We have raw data with duplicates in Excel format.

I want to summarise that in to table format first, and then later bring into Power APP so that user can see in order and enter spend values manually, and then when they click on submit it will update in reports.

 

Please let me know if  I am following or doing this method in wrong way. But I thought this is the only way I can acheive this because of manual entry data from end user.

 

I will get this Raw Excel data on monthly basis and we should summarise first with Power Automate I think.

 

currently we handling this in Excel and its too much time taking.

 

Thank you

 

 

 

 

@Sudhavi_84 

 

As long as these columns exist, you can create a measure to generate the last column. But I am only familiar with power bi, so it may be difficult to understand what you said in other aspects.

vjaneygmsft_0-1625212393178.png

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-janeyg-msft ,

 

Yes the highlighted columns are exist evrytime ongoing forward. But the highlighted column Values is entered manually by users and I think this is not possible by doing this in Power BI I think so? Please let me know if I am wrong

 

I want to make in Power BI/APP or  something that they can enter data manually and create visuals etc for this so they know what is happening.

 

 

@Sudhavi_84   I don’t understand what you mean. Entering data also means data change. Then after connecting the data, refresh it and you can see it. Isn't it?

Ahh I see where you coming from

 

Column A,B,C is coming from Database(Connectors and Done refresh) with that,  Column G,H and I can be seen in Power BI Reports.

 

But Column J to Column N is manual entry by users and this fields are not from database. This columns are created for Finanance dept only as they would like to understand where they spend and how they spend etc.. 

 

 

We have Power BI Reports which is perfect but it only shows what data we have in Database.

 

Additional columns which you highlighted they are for Finance dept and we don't have this fields in Database. 

 

We have other excel file Column G to O for Finance dept, so that they can see the split values on daily manual entry by Users.

 

This is why we created separate excel sheet for finance dept and we want to integrate this with Existing power BI reports with the help of Power APP.

Thank you and please let me know if I it is confusing.

@Sudhavi_84  You can put these data in excel to make a table, and then connect to use it. Data can come from multiple different data sources. 

@v-janeyg-msft ,

 

Yes I completely Agree, we already maintaining this data in Excel. But structure is completely different.

 

We have lots of other columns to compare like YTD,MTD,Y1,Y2 etc and we have like 450 Columns and 2000 rows..Its like template and we have other excel tabs too. Current excel file is reaching to 50MB in Sharepoint and it may not work if it is more than 50MB and the structure we have in Excel wont able to connect to Power BI because of structure of data.

 

We want to get rid of excel and allow users to enter manually in Power BI or something.

 

 

@Sudhavi_84  If the data needs to be continuously updated, it is impossible to manually enter it in powerbi.

If the file is large, the data must be filtered and cleaned before use. 

Sudhavi_84
Helper V
Helper V

Hi Please may I know how to summarise excel file to list Sharepoint list

 

Please see below my Input file in Excel sheet and I want in Sharepoint List as output with Power Automate/Power App

Sudhavi_84_0-1625129891420.png

 

Thank you and please let me know.

 

 

Hi, @Sudhavi_84 

 

In power bi, You can use table visual to display the result, it will automatically aggregate. But you haven’t mentioned power bi in the whole article, so I wonder if you post it in the wrong place.

Here is power app forum: Power Apps Community - Power Platform Community (microsoft.com)

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for sending me the Power APP link, I will update there.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.