Data Cleaning a matrix to import to PBI via PQ

[BigPatJennings]

Hi, I have a folder of Excel sheets, all identical in their layout, one for each season. The files are named for their season (i,e, PLScores 2022-23). Each one is in a matrix layout as you'll see from the example file I've uploaded. I've had quite a few attempts at trying to get this into PBI via Power Query, meging columns, transposing, unpivoting, etc ( I found Mynda Treacy's tutorial to be of help ) but I'm just not able to get this data into the correct layout for me to use in PBI. 

The end result I'd like to have is:

Season, Home Team, Away Team, Home Score, Away Score (I've included this layout in the example file for reference)

Can someone help me to shape this data in PQ, please?

Here's the file, and thanks very much!

https://docs.google.com/spreadsheets/d/15WbRrXNPTeBhgzokqEHLFI-_z3BQSJt3/edit?usp=sharing&ouid=10872... 

[v-cgao-msft]

Hi @BigPatJennings ，

Please refer to the steps in Power Query Editor.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[BigPatJennings]

Hi Gao, that's amazing. Your screenshot has the season in it, which I would like but can't see in your pbix. The season number can be found in the name of the Excel sheet (i.e. PLScores 2022-23). Can this be brought in, like your screenshot?

Also, and I haven't tested this yet, your solution works beautifully when there's one sheet. I have 30 in the folder location, one for each season. Will your solution work on multiple files like this? I'm just worried about whether column renaming might mess it up.

Thanks so much for taking the time, it's greatly appreciated.

[v-cgao-msft]

Hi @BigPatJennings ,

Sorry, this is the complete file. If the format of the original form is consistent, you don't need to make many changes.

The way I handle the season field is to add a custom column, which is not in the original table, so I don't know where to reference it, and there should be room for code optimization here.

Tip, next time posting your question to the appropriate forum will get better support.

Power Query - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[BigPatJennings]

Thanks very much, I truly appreciate it, and sorry for not posting in the correct forum.

This doesn't quite work, I'm afraid, because it doesn't take the name of the season from the filename (ee pi for folder contents), but I am grateful for you taking the time to try to help. I tried inserting a step to get the filename, which I could then grab the season from, but wasn't able to (well, I could get the filename, but then all the pivoting goes wrong!).

Anyway, I won't take up any more of your time.