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.
Hi
Question:
Table
Job_name, starttime, endtime
ods, 5/4/2018 01:01:23, 5/4/2018 01:02:40
ods, 5/4/2018 01:15:10, 5/4/2018 01:15:40
edw, 5/4/2018 01:04:18, 5/4/2018 01:06:54
edw, 5/4/2018 01:18:21, 5/4/2018 01:20:26
Answer:
i want to get report as:
ods and edw starttime and endtime in a same row with duration between (edw_starttime -ods_starttime)
5/4/2018 01:01:23, 5/4/2018 01:02:40, 5/4/2018 01:18:21, 5/4/2018 01:20:26, Duration
Can someone please help me..
Solved! Go to Solution.
Hi @prabhu_rathnam,
First, add an index column with below M code.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true), DUARTION_Sheet = Source{[Item="DUARTION",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(DUARTION_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job_name", type text}, {"starttime", type datetime}, {"endtime", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Job_name"}, {{"New Col", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded New Col" = Table.ExpandTableColumn(#"Grouped Rows", "New Col", {"starttime", "endtime", "Index"}, {"New Col.starttime", "New Col.endtime", "New Col.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded New Col",{{"New Col.starttime", "starttime"}, {"New Col.endtime", "endtime"}, {"New Col.Index", "Index"}}) in #"Renamed Columns"
Then, in data view, add a calculated column.
edw_starttime -ods_starttime = IF ( DUARTION[Job_name] = "edw", DATEDIFF ( CALCULATE ( MAX ( DUARTION[starttime] ), FILTER ( DUARTION, DUARTION[Index] = EARLIER ( DUARTION[Index] ) && DUARTION[Job_name] <> EARLIER ( DUARTION[Job_name] ) ) ), DUARTION[starttime], SECOND ) )
New a calculated table.
DUARTION2 = UNION ( SELECTCOLUMNS ( DUARTION, "Job_name", DUARTION[Job_name], "Index", DUARTION[Index], "starttime", DUARTION[starttime], "endtime", DUARTION[endtime] ), SELECTCOLUMNS ( DUARTION, "Job_name", "duration", "Index", DUARTION[Index], "startname", BLANK (), "endtime", DUARTION[edw_starttime -ods_starttime] ) )
Insert a matrix to display data from 'DUARTION2'.
Best regards,
Yuliana Gu
Hi @prabhu_rathnam,
First, add an index column with below M code.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true), DUARTION_Sheet = Source{[Item="DUARTION",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(DUARTION_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job_name", type text}, {"starttime", type datetime}, {"endtime", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Job_name"}, {{"New Col", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded New Col" = Table.ExpandTableColumn(#"Grouped Rows", "New Col", {"starttime", "endtime", "Index"}, {"New Col.starttime", "New Col.endtime", "New Col.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded New Col",{{"New Col.starttime", "starttime"}, {"New Col.endtime", "endtime"}, {"New Col.Index", "Index"}}) in #"Renamed Columns"
Then, in data view, add a calculated column.
edw_starttime -ods_starttime = IF ( DUARTION[Job_name] = "edw", DATEDIFF ( CALCULATE ( MAX ( DUARTION[starttime] ), FILTER ( DUARTION, DUARTION[Index] = EARLIER ( DUARTION[Index] ) && DUARTION[Job_name] <> EARLIER ( DUARTION[Job_name] ) ) ), DUARTION[starttime], SECOND ) )
New a calculated table.
DUARTION2 = UNION ( SELECTCOLUMNS ( DUARTION, "Job_name", DUARTION[Job_name], "Index", DUARTION[Index], "starttime", DUARTION[starttime], "endtime", DUARTION[endtime] ), SELECTCOLUMNS ( DUARTION, "Job_name", "duration", "Index", DUARTION[Index], "startname", BLANK (), "endtime", DUARTION[edw_starttime -ods_starttime] ) )
Insert a matrix to display data from 'DUARTION2'.
Best regards,
Yuliana Gu
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.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |