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
Joerobert
Helper II
Helper II

Power Query / Complex Data Transformation / Multiple Headers

Hello BI Community,

 

I am working with a data source that is excel based and I am very limited in the changes I can make with the structure. See below photos as a reference, photo 1 shows the scrubbed date version of the original table I am working with and photo 2 shows examples of the transformation I am trying to achieve. In reality, the original table contains hundreds of columns and contains both numerical/alphanumerical values in the cells and so I just want to tackle one problem at a time.

 

This table contains multiple headers and so I am trying to clean up the table so that it can look like either option #1 or option #2. I have been able to replicate option #1 however then I am faced with needing to duplicate the query and repeat the steps for the hundreds of columns. Has anyone successfully trasnformed a sheet similiar to the one shown below and if so, is there a particular technique you used to perform this over many columns?

 

Another option I was thinking is to create a unique column / query that displaying the well names and then integrate into the M-code an iterating step down through this column. Does anyone have any example M-code of interating steps to repeat a series of transformations & merges? If so, can I have a copy of that code?

 

Thanks in advance!

 

 

photo1.jpgphoto2.jpg

2 REPLIES 2
v-jiascu-msft
Employee
Employee

Hi @Joerobert,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ImkeF
Super User
Super User

For Option1: First you have to unpivot on the first column, adressing the first 2 rows as headers and then pivot back just with the values from the 2nd row.

 

Therefore you have to use my special function here: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power...

 

Use it like this:

 

image.png

 

let
    Source = fnUnpivotByNumbers(Table1, 1, 2),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Attrib.2]), "Attrib.2", "Value")
in
    #"Pivoted Column"

 

 

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.