splitting column to remove hierarchy in indented rows

Question

HI I have a csv file with the below layout and am trying to use power query to create a new set of columns so that it looks something like the second table, so i can then merge the two columns, I've tried transposing and pivoting but nothing seems to work! Any tips appreciated, thanks

MP at consulting room	32.60%
Attendance	5.77%
Attendance < 20mins	100.96%
Attendance > 20mins	73.08%
Attendance > 40mins	41.35%

MP at consulting room	MP at consulting room	32.60%
MP at consulting room	Attendance	5.77%
MP at consulting room	Attendance < 20mins	100.96%
MP at consulting room	Attendance > 20mins	73.08%
MP at consulting room	Attendance > 40mins	41.35%

freemanz · Answer

just add a column with content of "MP at consulting room" or?

davidm58 · Answer

ah sorry, i didnt make it at all clear that the 'MP at consulting '&nbsp; then changes, will update now&nbsp;

davidm58 · Answer

sorry the table has changing rows, eg: so i need the new column to change as well&nbsp;i tried to edit the post but couldnt work out how&nbsp;.&nbsp;MP at consulting room104&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance &lt; 20mins105&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance &gt; 20mins76&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance &gt; 40mins43MP not at consulting room0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance &lt; 20mins0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance &gt; 20mins0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attendance &gt; 40mins0

Forum Discussion

splitting column to remove hierarchy in indented rows

3 Replies

Related content

Creating a hierarchy from indented column

Date hierarchy

Indentity and surrogate key

Managed indentity and Power BI

Splitting Tag data