Noob needs help with shaping data

[Anonymous]

Hi all,

Noob here! I need advice on how to shape the following excel data structure for Power BI to make sense of. Please help?

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem at this link - Rearrange a multi heading dataset into a single heading one which is Pivot ready.

Hope this helps.

[Anonymous]

Awesome! Thank you so much!

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur 

Hi Ashish! Sorry to trouble you.

I am not clever enough to apply your example to my problem. There is an additional header row that I cannot figure out to transform. Can you take a look at the sample data file attached and provide a step by step? I will be most grateful!

DumpFile_Extract.xlsx

Note: Category 2 starts from column BN.

Regards,

Yee Pin

[Ashish_Mathur]

Hi,

My method will not work on your dataset because there are 3 headings for every column - in rows 1,2 and 3.  The only way my method can be made to work on your data is if we combine the headings in rows 1 and 2.  This way we will be left with only 2 headings per column.  In the final output though, we will again break up the combined strings and show them in seperate columns.  If you are agreable to this minor change in your inout data, then i can solve this for you.  Also, what is the use of the columns from FV:GM?  Can they be deleted?

[Anonymous]

Hi Ashish! Thank you so much for responding!

Yes, let's try the approach that you have selected. 

I have checked and Columns FV:GM are actually the comments for the Category. So in the data set, there are Safe and Unsafe comments for:

- General

- Category e.g. reactions of people, positions of people, etc

- Subcategory e.g. reactions of people - Adjusting PPE, reactions of people - rearranging job, positions of people - falling, positions of people - strking against or being struck by objects, etc.

Can we keep them?

[Ashish_Mathur]

Hi,

I am no sure of whether this is correct or not but refer to sheet3 of this workbook.

[Thim]

I would shape it like this, to put the catagories as par of the data. 

I have colour coded, so it is easier to see where the original data would be put.

Hope this will help.

[Anonymous]

Hi Thim,

Thank you so much! I am very grateful!

I am thinking that I will need to use "Unpivot" columns. Am I on the right track?