Forum Discussion

Honne2021's avatar
Honne2021
Helper II
4 years ago
Solved

How do I extract multiple dates in one cell using power query or dax?

How do I extract multiple dates in one cell using power query or dax? I had tried it via Split column delimeter / or - but it only extracted the first date. See table below for the example

Employee IDTitleNameLeavesTotal Leaves ConsumedTotal Remaining Leaves
1111StaffKim Ho01/05/2021 - 1 Days (Kim Ho)

01/06/2021 - 1 Days (Kim Ho)

01/07/2021 - 1 Days (Kim Ho)

01/08/2021 - 1 Days (Kim Ho)		411


The result I want to get is:

Employee IDTitleNameLeavesTotal Leaves ConsumedTotal Remaining Leaves
1111StaffKim Ho01/05/2021411
1111StaffKim Ho01/06/2021411
1111StaffKim Ho01/07/2021411
1111StaffKim Ho01/08/2021411
General Comment
Need Help
Tutorial Requests
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.RemoveItems(Text.Split(_{3},"#(lf)"),{null,""}),(x,y)=>List.FirstN(x,3)&{Date.From(Text.Split(y,"-"){0})}&List.Skip(x,4)))

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.RemoveItems(Text.Split(_{3},"#(lf)"),{null,""}),(x,y)=>List.FirstN(x,3)&{Date.From(Text.Split(y,"-"){0})}&List.Skip(x,4)))