Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Chthonian
Helper III
Helper III

Transpose wide excel table to long table with blocks of columns

Hello my fello Datanuts,

 

I am looking for some guidance on the best way to transpose a wide excel dataset into a long table in powerquery. 

 

Background

I have inherited a profitability report that is fed from a spreadsheet populated by the most obscure and complex SQL setup I have ever seen (and I am not a SQL expert (yet!)). I know these things are always best done at the source, I am just unable to decode the horrible mess that was created before time began 😖

 

The Challenge

The first 4 columns of the data are company name a a few ID columns, which are then followed by blocks of columns (6 Cols) which hold the data for a particular month identified by a Billing Period ID, and each subsequent billing period has the same set of columns. And then at the end of the wide table I have a few other date fields that I need to keep.

 

The Data

Download Excel File

 

My Dirty Fix

I did manage to get a dirty solution in place but it did not feel right, elegant or even dynamic enough given that there will be a number of excel files to merge into a single report.

  1. Create an Index Column (Company ID)
  2. Duplicate query and remove all billing period blocks (This became my new base table)
  3. Duplicate orignal table and remove all other columns except COMPANY ID and 1st Billing Period Block of Columns
  4. Repeat for all Billing Periods
  5. Append all billing periods to base table

While this may work in a most simplistic way, i am sure that there is a better way to achieve this, and I am hoping you folks can offer some guidance. Especially given that I would ideally like this to work for multiple excel files.

 

Thanks in advance.

David

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@Chthonian 

I did it using all GUI steps except for one small coding. Please check and let me know.


You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn




 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@Chthonian 

I did it using all GUI steps except for one small coding. Please check and let me know.


You can download the file: HERE

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn




 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @Fowmy

 

Thats a much much cleaner way than I did it 🙂 and I can easily follow the steps in that.

 

Going to have a play but really appreciate the assistance sir!

 

Thanks a million,

David

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors