Forum Discussion
Split column then pivot back
Hello,
Unfortunately I have a lot of messy data to cleanse.
We get job order information with the date, job number, products (anywhere from 1 to 5 products) and quantities associated with the aforementioned products.
I have managed to split out the cell to multiple columns by using the split by delimiter function. However the desired outcome would have all products in one column and it's corresponding quanitity in another. I have tried pivoting/unpivoting, but I'm lost. Would appreciate any ideas how to tackle this.
Thanks
NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.Zip(List.Transform(List.Skip(_,2),each Text.Split(_,"/"))),(x,y)=>List.FirstN(x,2)&y))
4 Replies
NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.Zip(List.Transform(List.Skip(_,2),each Text.Split(_,"/"))),(x,y)=>List.FirstN(x,2)&y))
You're a wizard! Thank you so much. Works perfectly!!
As I only provided a sample of the data in my original post, I've gone on to attempt to use it with live data and there are some cells (in other columns) that are blank (null) in the query and that is causing it to error. Is there a way I can work around this without having to get rid of the null values. For example, there will not always be a job number assigned so the cell will be blank..
Thanks again for the start on the code.
NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.Zip(List.Transform(List.Skip(_,2),each if _=null then {null} else Text.Split(_,"/"))),(x,y)=>List.FirstN(x,2)&y))
