Forum Discussion

JNelson's avatar
JNelson
Icon for Helper II rankHelper 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))

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
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
Icon for Helper II rankHelper II
3 years ago

You're a wizard! Thank you so much. Works perfectly!!