Construction example data - need to split data from columns into rows

[ndubsj]

Hi All,

This one has been breaking me! Below (figure 1) is an example set of data in a standardised format that we use on construction projects. This is obviously great and detailed but has issues when analysing, namely that the values are only on some but not all levels and the spacing of those is not consistent. What I would like to be able to do is a quick way to convert data in figure 1 to that in figure 2 if anyone has an idea on how to approach. I've tried some PowerQuery options but struggling to keep all the required data. Thanks in advance if anyone is able to assist!

Figure 1 (example data):

Figure 2 (desired output):

Also asked here Construction example data - need to split data from columns into rows

 

[kvsrinivasamurthy]

Add more data with full expected result.

 

[HighAndWilder]

Hi All,

This one has been breaking me! Below (figure 1) is an example set of data in a standardised format that we use on construction projects. This is obviously great and detailed but has issues when analysing, namely that the values are only on some but not all levels and the spacing of those is not consistent. What I would like to be able to do is a quick way to convert data in figure 1 to that in figure 2 if anyone has an idea on how to approach. I've tried some PowerQuery options but struggling to keep all the required data. Thanks in advance if anyone is able to assist!

Figure 1 (example data):
View attachment 119179

Figure 2 (desired output):
View attachment 119178

Also asked here Construction example data - need to split data from columns into rows

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB - Excel Range to BBCode

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.