Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ID | Title | Name | Leaves | Total Leaves Consumed | Total Remaining Leaves |
1111 | Staff | Kim Ho | 01/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) | 4 | 11 |
The result I want to get is:
Employee ID | Title | Name | Leaves | Total Leaves Consumed | Total Remaining Leaves |
1111 | Staff | Kim Ho | 01/05/2021 | 4 | 11 |
1111 | Staff | Kim Ho | 01/06/2021 | 4 | 11 |
1111 | Staff | Kim Ho | 01/07/2021 | 4 | 11 |
1111 | Staff | Kim Ho | 01/08/2021 | 4 | 11 |
Solved! Go to Solution.
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)))
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)))
Thank you! That worked for me!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.