cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SPS-DEV-22
Helper III
Helper III

How to convert spreadsheet to Power App connected to SharePoint list

I have an excel spreadsheet that been asked to make it a power app connected to SharePoint list.

I was wondering how would I handle the fact that in a spreadsheet there is a Check in and Check out process that can happen lets say 20 times (multiple days/possible same day) on the same Item #..  There are additional fields that would be filled on besides the 3 shown.  but was wondering how do I setup the SharePoint List columns? 

 

Do I create Date & Time | Action 1 |Action 2 |  and then add columns 1 thru 20? and then on Power App make it a check box (conditional button when clicked the data fields for the new days appear on the form)  which would then bring in the next date and time with the fields to fill in additional days for the item

Example:

Date & Time2 ..... Date and Time20 |Action1_2 .... Action1_20 | Action2_2 .... Action2_20 | 

 

Item # USDA001

Date and Time Action 1  Action 2
1/1/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf
1/2/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf
1/3/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf
1/4/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf

 

or is there a different way to achieve the data entry of the  Action items multiple times and get the data saved for each day .

 

thanks for any advice and or ideas to solve the issue is appreciated.

9 REPLIES 9
Drrickryp
Super User
Super User

@SPS-DEV-22 

Here is a sample asset management database that uses three tables.  It may have more than  you need but it is a start.

asset management.jpg

SPS-DEV-22
Helper III
Helper III

I'm using a SharePoint list for the data.  thanks for the SQL data idea. that is why I was asking about the power app and SharePoint list having the # of columns matching depending on how many times the item was checked in and checked out for the various days

Split your data by the columns and import that into a Sharepoint list (or paste in the Grid view data). This usually can build a relationship around an equipment ID/Asset ID in a simple manner. A thing that could be useful is the (if your interested in switching over to Dataverse through a Dataflow, or to keep it local in Power Query in Excel) transofrmation acheived by unpivoting data, to bring about your desired setup. It may seem like a hack but if you simply unpivot those three columns you have there, and then filter the data for the specific attribute to save as a new table. You would have four tables, Assets, Dates, Action 1, Action 2.

SPS-DEV-22
Helper III
Helper III

I'm not using the equipment ID/Asset # item, my issue is at the very top with the Action 1 Action 2 Item  and that there are several days where that action can take place for the same item #.

I'm just trying to figure out how the powerapps form/sharepoint list can handle the check in /check out of the item multiple times over a course of 20 days and save that data each time

But you would ideally want the data in an expanded format, where the index number is attached to all the necessary datetimes or actions. Only then can we use the lookup formula to gather related information.

So, what you are saying is to create 18 columns?

Item # USDA001

Date and Time Action 1  Action 2
1/1/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf
1/2/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf
1/3/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf
1/4/1900 Removed from Box  Returned to Shelf
  Returned to Box  Remove from Shelf


these are the columns that repeat, and I was told possible up to 18 times in a month.

this can repeat 18 times. where each time it repeats it is a new Date and Time and then the columns are filled out for that day, by the input person.
There are other columns in the SharePoint list it will have lots of columns but those above will repeat until the item closes out.  Not sure if this is going to require 2 SharePoint lists, or if I can get away with 1 SharePoint list holding all the data.

I would prefer to use 1 SharePoint list with a Power App Attached, where there is the new, edit, display forms for the item #.

 

Any help or direction would be great.

I was saying to do a power query unpivot to translate into a filtering capability in powerapps.
Item #       Date and Time   Attribute    Value

USDA00101/03/22 0:00Action 2Returned to Shelf
USDA00101/03/22 0:00Action 1Returned to Box
USDA00101/03/22 0:00Action 2Remove from Shelf
USDA00101/04/22 0:00Action 1Removed from Box
USDA00101/04/22 0:00Action 2Returned to Shelf
USDA00101/04/22 0:00Action 1Returned to Box
USDA00101/04/22 0:00Action 2Remove from Shelf

I'm trying to move away from the Excel File. As that is what the client wants to do.

 

So, I don't think this will work, power query unpivot to translate into a filtering capability in powerapps, plus I never done a power query unpivot 

The least you could do is "fill down" manually in Excel for each Item # in a new column. So you still have to deal with the variable number of columns, all the way up to Action 18. I would separate the action table in a new list. Copy and paste it or separate in different Excel sheets for sharepoint import. This will normalize the data a little bit, but manually.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (3,222)