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
BigPatJennings
Frequent Visitor

Data Cleaning a matrix to import to PBI via PQ

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

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @BigPatJennings ,

 

Please refer to the steps in Power Query Editor.

vcgaomsft_0-1662013858938.png

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

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.

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.

vcgaomsft_0-1662025990874.png

 

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

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.

BigPatJennings_0-1662032668903.png

 

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.