cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors