Smartly Splitting Cells into Rows delimited by space

[Erika_S]

hi all,

Hope someone would be able to help me. What i am trying to do is split values in the cell into rows. But one more complexity is thae once i split it - it needs to depend on the next columns split value. Not sure i am explaining it well. Here is what data looks like: and bellow - how it would like it to look like. 

I successfuly split cells to rows in powery query. But doesnt do it smartly... Comes back like this:

Any advice would be very very very 🙂 helpful.

Thank you guys,

erika

[HotChilli]

There was a similar challenge on excelguru website just  a few days ago.

Have a look here

https://www.excelguru.ca/blog/2019/08/01/solutions-for-power-query-challenge-6/

and solutions

https://www.excelguru.ca/blog/2019/08/01/solutions-for-power-query-challenge-6/

Basically there are two solutions(one that can be done with point and click and one that requires deeper M knowledge)

The point and click one splits the column on linefeed character then filters out unwanted rows

[Erika_S]

Thank you definately useful tips, but it didnt work for my file. Could not pass through the step of Grouping it by. Some more screenshots would be really helpful... 

It just kept saying cant group as it cant convert one of the values... Could the issue be that there are extra blank lines in the cell? But i just remove those as "remove empty".

Sorry if it seems a simple task, but i really struggle to make this one work and it is very important report to. Im sure it should be possible to not do it manualy? 🙂

[HotChilli]

Please post your data(not a picture) or pbix.  and can you tell me which of the solutions you tried?

It's not a simple task