Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi - not sure how complexed this is
i have a table where i have
Start Date Time End Date Time
19/12/23 22:35. 20/12/23 01:00
20/12/23. 08:00. 20/12/23 16:00 etc
all i want to do is if the start date time and end time overlap i need to separate them into its own rows else keep the row so it should look like this
first row overlaps over 2 days so i split till midnight and then follow on from
midnight
19/12/23 22:35. 19/12/23 00:00
20/12/23 00:00 20/12/23 01:00
20/12/23. 08:00. 20/12/23 16:00
hope this makes sense
Solved! Go to Solution.
Hi @Mahmed1 ,
Apologise!
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzTSNzJWMDKyMjZV0lEyMoAKGBhaGRgoxepEw4X0FAwsQGJIigzNwIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date Time" = _t, #"End Date Time" = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Start Date Time.1", "Start Date Time.2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"End Date Time.1", "End Date Time.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Start Date Time.1", "Start Date"}, {"Start Date Time.2", "Start Time"}, {"End Date Time.1", "End Date"}, {"End Date Time.2", "End Time"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each { Number.From ( [Start Date] ) ..Number.From ( [End Date] ) }), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Start Date Time", each if [Start Date] = [Custom] then Text.From ( [Start Date] ) & " " & Text.From ( [Start Time] ) else Text.From ( [End Date] ) & " 00:00"), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date Time", each if [End Date] = [Custom] then Text.From ( [End Date] ) & " " & Text.From ( [End Time] ) else Text.From ( [End Date] ) & " 00:00"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "Start Time", "End Date", "End Time", "Custom"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"End Date Time", type datetime}, {"Start Date Time", type datetime}}) in #"Changed Type2"
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
With Duration.TotalMinutes
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Add_Date = Table.AddColumn(Source, "Date_",
each List.Dates(
Date.From([Start Date Time]),
Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,
#duration(1,0,0,0))),
Date_Expand = Table.ExpandListColumn(Add_Date, "Date_"),
Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date_", type datetime}}),
#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2",
each List.Max({[Start Date Time],[Date_]})),
#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2",
each List.Min({[End Date Time],[Date_]+#duration(1,0,0,0)})),
Total_Minutes = Table.AddColumn(#"End Date Time 2", "Total_Minutes",
each Duration.TotalMinutes([End Date Time 2]-[Start Date Time 2]))
in
Total_Minutes
Stéphane
With Duration.TotalMinutes
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Add_Date = Table.AddColumn(Source, "Date_",
each List.Dates(
Date.From([Start Date Time]),
Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,
#duration(1,0,0,0))),
Date_Expand = Table.ExpandListColumn(Add_Date, "Date_"),
Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date_", type datetime}}),
#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2",
each List.Max({[Start Date Time],[Date_]})),
#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2",
each List.Min({[End Date Time],[Date_]+#duration(1,0,0,0)})),
Total_Minutes = Table.AddColumn(#"End Date Time 2", "Total_Minutes",
each Duration.TotalMinutes([End Date Time 2]-[Start Date Time 2]))
in
Total_Minutes
Stéphane
Thank you everyone
there are a few solutions here which one is the best and efficient one to use if i have a large data set
Hi,
another solution
let
Source = YourSource
Add_Date = Table.AddColumn(Source, "Date",
each List.Dates(
Date.From([Start Date Time]),
Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,
#duration(1,0,0,0))),
Date_Expand = Table.ExpandListColumn(Add_Date, "Date"),
Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date", type datetime}}),
#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2",
each List.Max({[Start Date Time],[Date]})),
#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2",
each List.Min({[End Date Time],[Date]+#duration(1,0,0,0)}))
in
#"End Date Time 2"
Stéphane
Thanks to everyone
it worked beautifully - i trued to add a difference minutes column in power query but it doesnt work when minusing from midnight
please see image of what its returning and code (it should be 60 minutes for first row)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Add_Date = Table.AddColumn(Source, "Date_",
each List.Dates(
Date.From([Start Date Time]),
Duration.Days(Date.From([End Date Time])-Date.From([Start Date Time]))+1,
#duration(1,0,0,0))),
Date_Expand = Table.ExpandListColumn(Add_Date, "Date_"),
Type_datetime = Table.TransformColumnTypes(Date_Expand,{{"Date_", type datetime}}),
#"Start Date Time 2" = Table.AddColumn(Type_datetime, "Start Date Time 2",
each List.Max({[Start Date Time],[Date_]})),
#"End Date Time 2" = Table.AddColumn(#"Start Date Time 2", "End Date Time 2",
each List.Min({[End Date Time],[Date_]+#duration(1,0,0,0)})),
#"Reordered Columns" = Table.ReorderColumns(#"End Date Time 2",{"Date_", "Start Date Time", "End Date Time", "Start Date Time 2", "End Date Time 2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date_", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Start Date Time 2", "Start_Time"}, {"End Date Time 2", "End_Time"}}),
#"Inserted Time" = Table.AddColumn(#"Renamed Columns", "Time", each DateTime.Time([Start_Time]), type time),
#"Inserted Time1" = Table.AddColumn(#"Inserted Time", "Time.1", each DateTime.Time([End_Time]), type time),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Time1",{"Start Date Time", "End Date Time", "Start_Time", "End_Time"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Time", "Start_Time"}, {"Time.1", "End_Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Start_Time", type number}, {"End_Time", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Number.Mod([End_Time]-[Start_Time],1)*1440),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"End_Time", type time}, {"Start_Time", type time}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Total_Minutes"}})
in
#"Renamed Columns2"
thnks
add custom column
Change [Start Date Time] and [End Date Time] to your column names
List.Generate(()=>
[start = [ Start Date Time], end = Date.From(start) & #time(24,0,0) ],
(x) => x[end] < [End Date Time] + #duration(1,0,0,0),
(x) => [ start = x[end], end = Date.From(start) & #time(24,0,0) ],
(x) => [ start = x[start], end = List.Min({x[end], [End Date Time]}) ]
)
Thank you so much - ill give that a go
Just by looking at the 2nd row - the start date time should be 20/12 as its now from 00:00 till 01:00 so shouldnt say 19/12 for that row
Hi @Mahmed1 ,
Apologise!
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzTSNzJWMDKyMjZV0lEyMoAKGBhaGRgoxepEw4X0FAwsQGJIigzNwIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date Time" = _t, #"End Date Time" = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Start Date Time.1", "Start Date Time.2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"End Date Time.1", "End Date Time.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Start Date Time.1", "Start Date"}, {"Start Date Time.2", "Start Time"}, {"End Date Time.1", "End Date"}, {"End Date Time.2", "End Time"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each { Number.From ( [Start Date] ) ..Number.From ( [End Date] ) }), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Start Date Time", each if [Start Date] = [Custom] then Text.From ( [Start Date] ) & " " & Text.From ( [Start Time] ) else Text.From ( [End Date] ) & " 00:00"), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date Time", each if [End Date] = [Custom] then Text.From ( [End Date] ) & " " & Text.From ( [End Time] ) else Text.From ( [End Date] ) & " 00:00"), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "Start Time", "End Date", "End Time", "Custom"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"End Date Time", type datetime}, {"Start Date Time", type datetime}}) in #"Changed Type2"
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Mahmed1,
How about this:
Here the M code that you can paste into the advanced editor. You can check out the steps on the right to get a grasp on what's going on 🙂
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUNzTSNzJWMDKyMjZV0lEyMoAKGBhaGRgoxepEw4X0FAwsQGJIigzNwIpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date Time" = _t, #"End Date Time" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Start Date Time.1", "Start Date Time.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"End Date Time.1", "End Date Time.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Start Date Time.1", "Start Date"}, {"Start Date Time.2", "Start Time"}, {"End Date Time.1", "End Date"}, {"End Date Time.2", "End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Date", type date}, {"Start Time", type time}, {"End Date", type date}, {"End Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each { Number.From ( [Start Date] ) ..Number.From ( [End Date] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Start Date Time", each if [Start Date] = [Custom] then Text.From ( [Start Date] ) & " " & Text.From ( [Start Time] ) else Text.From ( [Start Date] ) & " 00:00"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "End Date Time", each if [End Date] = [Custom] then Text.From ( [End Date] ) & " " & Text.From ( [End Time] ) else Text.From ( [End Date] ) & " 00:00"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Start Date", "Start Time", "End Date", "End Time", "Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"End Date Time", type datetime}, {"Start Date Time", type datetime}})
in
#"Changed Type2"
Let me know if this solved the issue, by accepting the answer as the solution. Tag me if you have a question 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |