Forum Discussion

young351's avatar
young351
Frequent Visitor
3 years ago
Solved

Need to Parse data into columns/rows

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:...
Need Help
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    Hi young351 ,

     

    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:

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

v-xiaosun-msft's avatar
v-xiaosun-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi young351 ,

 

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:

 

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.