Need to Parse data into columns/rows

Question

I have an .xlsx file I am pulling in from Sharepoint. I need to parse out the last column into seperate columns/rows, for example:

Date: - GEN ID: - Comments: - Vendor Downtime + Customer Downtime: - Unplanned: - Vendor: - Total Hrs Down Customers:

All need to be columns, but the text following needs to be the values in the rows.

Below is the view from the .xlsx file on the left and the right is how Pbi is pulling it in.

v-xiaosun-msft · Accepted Answer

Hi&nbsp;young351&nbsp;,
&nbsp;
Take the "body" column out separately and deal with it. I created a sample.

Step 1: use "replace value" in Power Query.
Add "," in front of the content which you want to use as title. For example, "Vendor:" replaced with ",Vendor:".

Step2: Then use "Split Columns" in Power Query to split each column into rows by ":".

You will get followings:

Step3: Use first row as title and then keep the last row, then you will get the expected output.

Final output:

&nbsp;
Best Regards,Community Support Team _ xiaosun
If this post&nbsp;helps,&nbsp;then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;