Dear All
I want to get formula to calculate duration per days to complete each task.
Reported Date – Actual Finish Date : For Example 3 days and 45 minutes etc..
Regards;
Dear All
I want to get formula to calculate duration per days to complete each task.
Reported Date – Actual Finish Date : For Example 3 days and 45 minutes etc..
Regards;
Using Power Query/Get and Transform, here is the Mcode generated by that action.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Reported Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Reported Date.1", "Reported Date.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Reported Date.1", type text}, {"Reported Date.2", type time}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Actual Finish", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Actual Finish.1", "Actual Finish.2"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Reported Date.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Reported Date.1.1", "Reported Date.1.2", "Reported Date.1.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Reported Date.1.1", Int64.Type}, {"Reported Date.1.2", Int64.Type}, {"Reported Date.1.3", Int64.Type}, {"Actual Finish.1", type text}, {"Actual Finish.2", type time}}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type1", "Actual Finish.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Actual Finish.1.1", "Actual Finish.1.2", "Actual Finish.1.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Actual Finish.1.1", Int64.Type}, {"Actual Finish.1.2", Int64.Type}, {"Actual Finish.1.3", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Reported Date.1.2", type text}, {"Reported Date.1.1", type text}, {"Reported Date.1.3", type text}}, "en-US"),{"Reported Date.1.2", "Reported Date.1.1", "Reported Date.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Reported Date"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Reported Date.2", type text}}, "en-US"),{"Reported Date", "Reported Date.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Reported DateTime"),
#"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Actual Finish.1.2", type text}, {"Actual Finish.1.1", type text}, {"Actual Finish.1.3", type text}}, "en-US"),{"Actual Finish.1.2", "Actual Finish.1.1", "Actual Finish.1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns2",{{"Merged", type date}}),
#"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type3", {{"Merged", type text}, {"Actual Finish.2", type text}}, "en-US"),{"Merged", "Actual Finish.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Actual Fininsh DateTime"),
#"Changed Type4" = Table.TransformColumnTypes(#"Merged Columns3",{{"Reported DateTime", type datetime}, {"Actual Fininsh DateTime", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type4", "Duration", each [Actual Fininsh DateTime]-[Reported DateTime]),
#"Inserted Days" = Table.AddColumn(#"Added Custom", "Days", each Duration.Days([Duration]), Int64.Type),
#"Inserted Hours" = Table.AddColumn(#"Inserted Days", "Hours", each Duration.Hours([Duration]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Duration]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Minutes",{"Duration"})
in
#"Removed Columns"
Display More
Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
Display More
Don’t have an account yet? Register yourself now and be a part of our community!