How to convert spreadsheet to Power App connected to SharePoint list

[SPS-DEV-22]

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.

[Drrickryp]

@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.

[SPS-DEV-22]

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

[jaeiow]

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]

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

[jaeiow]

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.

[SPS-DEV-22]

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.

[jaeiow]

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

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

[SPS-DEV-22]

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 

[jaeiow]

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.