Forum Discussion

JNelson's avatar
JNelson
Helper II
3 years ago
Solved

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 a...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    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))

JNelson's avatar
JNelson
Helper II
3 years ago

Hi wdx223_Daniel 

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.

 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

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))