Pivot Table for Casino log

[acura123]

So I am trying to create a Casino log to track my trips in Excel. My data is entered so that the Column headings go along the first column and the Trip Dates go along the top row.
The problem I'm having is how do I perform a pivot table on this data in this format so that I can generate useful information like total win/loss to Date or amount of food Comped?
Does the Data have to be rearranged or can it be used in it's current format?

Sample Casino Diary.xlsx
	B	C	D	E	F	G
2	Date	1/1/2021	1/15/2021	1/20/2021	2/1/2021	3/1/2021
3	Casino	Harrrahs	Ballys	Borgata	Harrahs	Tropicana
4	Win Loss	-100	300	250	-50	0
5	Food	10	20	50	100	25
Sheet1

Thank You!

 

[mrshl9898]

Best to Rearrange it to 4 columns. Casino/Win Loss/Food/Date

 

[acura123]

Best to Rearrange it to 4 columns. Casino/Win Loss/Food/Date

That's what I have been doing. Thought there was an easier way to run a pivot table in that format.

 

[mrshl9898]

So unlike the 4 Rows you showed? In 4 columns you should have no issues with Pivoting the data

 

[alansidman]

If this is what you want, then I rearranged your data in Power Query with the following Mcode

Book5
	F	G	H
1	Casino	Sum of Win Loss	Sum of Food
2	Ballys	300	20
3	Borgata	250	50
4	Harrahs	-50	100
5	Harrrahs	-100	10
6	Tropicana	0	25
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Casino", type text}, {"Win Loss", Int64.Type}, {"Food", Int64.Type}})
in
    #"Changed Type1"

which arranged your data to look like this

Book5
	A	B	C	D
1	Date	Casino	Win Loss	Food
2	1/1/2021	Harrrahs	-100	10
3	1/15/2021	Ballys	300	20
4	1/20/2021	Borgata	250	50
5	2/1/2021	Harrahs	-50	100
6	3/1/2021	Tropicana	0	25
Table1

and then just pivot the data as you normally would.

 

[acura123]

So to be clear you can't pivot data when it is formatted like I first listed?

 

[mrshl9898]

You can pivot any table, but the headers will determine what fields are in there. (the top row)

 

[alansidman]

So to be clear you can't pivot data when it is formatted like I first listed?

You can certainly pivot the data from your table, however, it will not give you your expected results. You will need to change the format of your current table.

 

[acura123]

So what I did was create a table out of the original data and then created a separate sheet with a transposed the table so that the data labels are across the top so that they can easily be pivoted.

 

[alansidman]

I believe I did what you want in Post #5. Advise if you are looking for something different.