Messy excel file with stacked data in non excel tables|Power Query|Excel Forum|My Online Training Hub

Hi John,

It is possible, but not easy.
To separate weeks, you can add an index column and a custom column to identify the beginning of another section, based on text "IPS CODE" from column 1.

If you fill down the Custom column, you will be able to group rows into different tables.

That was the easy part...

Each table must be now converted.

I used a double loop in the custom function that converts the tables:
Loops = List.Accumulate({8..21},Data1,(L1State,i)=>
                                    List.ReplaceRange(L1State,i-1,1,

                                {List.Accumulate({0..RowsCount},{},(L2State,j)=>
                                            if Text.Contains(if Data1{0}{j}=null then "" else Data1{0}{j},"CODE") then
                                           L2State & {Text.From(L1State{i-1}{j}) & "|" & Text.From(L1State{i-1}{j+2})}
                                           else L2State & {L1State{i-1}{j}})})),

The double loop is similar to what is usually done in vba:

For i=column8 to column21

     For j=0 to tableRowsCount-1

          if column 1 value contains "CODE" then replace date with Date & "|" & the value located 2 rows down (where we have EST or ACT)

     Next j

Next i

There can be other solutions, it's just an idea.

You're welcome.
Anyway, it's not about M knowledge. In fact, the double loop is an unnecessary complication, the function can be created without editing M code, using user interface tools.

It's just imagination I think, to let imagination fly, you have to think different than normal excel tools.

Here is a function built with interface, will do the same thing (Est and ACT are not independent columns, they are attributes in the same column. It can be pivoted to make 2 columns though):