Data Shaping with M question

[vanessafvg]

I have an excel file that is a template for inputting reports on fault equiment.

because its a form the data looks a lot like below, there is a category and then a value in the same role underneath, and in the same column are many categories with values.   How do you go about shaping this so that the category becomes the colum and the value underneath the value, it would be be easy if it was only one category per column but because there are multiple it is not so simple.  Also how can you grab the line after the category?

thanks!

I have 

[Vvelarde]

@vanessafvg

Hi, please try following the steps of the image:

Regards

Victor

Lima - Peru

[mjht07]

@Vvelarde  how can I slow down you demonstration video so I can follow along with the steps?  I am new to this and it goes too fast for me to absorb

[vanessafvg]

@Vvelarde you rock will give that a go

[vanessafvg]

@Vvelarde ah sorry i actual over simlified the requirements, the issue is the issue of the column is spread across multiple columns that may or may not have data in them and may or may not have different fields

for example it looks a bit more like this

[Vvelarde]

@vanessafvg

Ohh, well I will try. in your scenario what is the expected result.

With 2 cases and 2 results will be very helpful to me and others.

Victor

[vanessafvg]

expected result would be to have all fields assigned their own columns with their values if there is on and null if there isn't

as you could see with the work details, the value isn't always directly below the field name

does that make sense @Vvelarde

thank you again

[Vvelarde]

@vanessafvg

I almost close but now going to a meeting. 

But i attached my file. 

[vanessafvg]

thank you so much, that was really useful i learned a lot, unfortunately once again trying ot protect the content of the data ive understated the requirements;

1) you can get more than one field on the same row so when you combine columns you have up to 4 columns on the same row with lots of gaps between

2) there are hundreds of these templates that need to  be combined, so doing a group by on the field name wont work because you wont know which form it came from

i learned a lot from what you posted  but struggling with the issues from above

this is an exapmle the actual data below , as i said here are many forms so thats just the first one sucked in by power query.

[vanessafvg]

@Vvelarde hi sorry i dont know if you saw the udpate to my post, sorry to be a pain in the butt, any idea you know how to handle the above scenario

[Vvelarde]

@vanessafvg

Hi, sorry for my delay.  Looking the complexity of your template / data structure maybe you'll need some Code directly in Advance Editor.   @ImkeF This is a puzzle that maybe you can help to resolve.

Regards

[ImkeF]

Hi @vanessafvg,

please post representative sample data of before and after.

Thanks and cheers, Imke

[vanessafvg]

@ImkeF thanks will put something together tomorrow, not much to show for after to be honest because finding it hard to pull the data vertically and horizontally.

[ImkeF]

After is important, because it is not clear to me from the screenshots you showed, which item belongs to wich header/category. 

[vanessafvg]

@ImkeF ah i see what you saying, the expected result,  yes makes absolute sense.

[jcarville]

@Vvelarde, nice one! I was having a go at this myself and was waiting for a solution.