Hi John,

I'm not sure there's an easy way to fix this data because there's a different number of rows in each group of weeks. i.e. there's no consistent pattern. My approach would be to create a separate table for each Sun-Sat range of rows, unpivot and then append.

Someone else might have a better idea.

Mynda

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.

Thanks Catalin, once again your awesome grasp of M comes to the rescue, next time I'm allowed into Ro I'll buy you a beer or two.

John

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):