Power Query / Complex Data Transformation / Multiple Headers

[Joerobert]

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!

[v-jiascu-msft]

Hi @Joerobert,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[ImkeF]

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:

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