Power query to get data

[Satheesh9012]

Hello all,

There is report where i am working on requires help in making the data look simple, i have a raw data as a first step i need to change the time zone to IST(Indian standard time zone), for which the data comes along with the date, second step is i need to split join time and leave time which comes one below the other in the same raw data file, there are chances same person joining time will be there in two rows with two different time and there are left time also mentioned subsequently, after the split of join time and leave time in two different columns i need to calculate the duration between join and wait time

I am attaching the screen shot 1 which is the raw data file, using power query i need to change it as it looks in the screen shot 2, using power query the out put should like screen shot 2.


Any help for my query raised will make my life simple in working

Thanks
Satheesh

 

[VBA learner ITG]

Hello all,

There is report where i am working on requires help in making the data look simple, i have a raw data as a first step i need to change the time zone to IST(Indian standard time zone), for which the data comes along with the date, second step is i need to split join time and leave time which comes one below the other in the same raw data file, there are chances same person joining time will be there in two rows with two different time and there are left time also mentioned subsequently, after the split of join time and leave time in two different columns i need to calculate the duration between join and wait time

I am attaching the screen shot 1 which is the raw data file, using power query i need to change it as it looks in the screen shot 2, using power query the out put should like screen shot 2.


Any help for my query raised will make my life simple in working

Thanks
Satheesh

I am not proficient in Power Query but do you mean something like this?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"email", type text}, {"full name", type text}, {"time stamp", type datetime}, {"action", type text}, {"role", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "time stamp", "time stamp - Copy"),
    #"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"time stamp - Copy"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[action]), "action", "time stamp"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Duration", each [left]-[joined]),
    #"Inserted Minutes" = Table.AddColumn(#"Added Custom", "Minutes", each Duration.Minutes([Duration]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Minutes",{"Minutes"})
in
    #"Removed Columns"

 

[Satheesh9012]

I am not proficient in Power Query but do you mean something like this?

Thank you for your reply , i shall insert this complete step into the query editor in the formulae tab, i hope that would do right or you want me to perform these steps as you have mentioned?

 

[VBA learner ITG]

Thank you for your reply , i shall insert this complete step into the query editor in the formulae tab, i hope that would do right or you want me to perform these steps as you have mentioned?

If you perform the steps listed it should help you get to your final output

 

[Satheesh9012]

If you perform the steps listed it should help you get to your final output

Thank you for your note i performed the steps, i am getting an error may be i am missing something here, and when i did the pivot it becomes like the screen shot attached also attaching the error screen,

Could you please help, i understood the logic but may i am doing the steps wrong, if i could achieve it would be more helpfull

 

[VBA learner ITG]

You can paste this and it should do everything:

I would send the file to you but this site doesn't allow me to upload the file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Full Name", type text}, {"Timestamp", type datetime}, {"Action", type text}, {"Role", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Action]), "Action", "Timestamp"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Full Name", "Joined", "left", "Email", "Role"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Email", "Participant ID"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Joined", "Joined - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Joined - Copy", "Joined Time"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Full Name", "Joined", "Joined Time", "left", "Participant ID", "Role"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Joined Time", type time}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "left", "left - Copy"),
    #"Reordered Columns2" = Table.ReorderColumns(#"Duplicated Column1",{"Full Name", "Joined", "Joined Time", "left", "left - Copy", "Participant ID", "Role"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"left - Copy", "Left Time"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Left Time", type time}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each [Left Time] - [Joined Time], type duration),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Duration"}}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns3",{"Full Name", "Joined", "Joined Time", "left", "Duration", "Left Time", "Participant ID", "Role"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns3",{"Left Time", "Joined Time"})
in
    #"Removed Columns"

 

[Satheesh9012]

You can paste this and it should do everything:

I would send the file to you but this site doesn't allow me to upload the file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Full Name", type text}, {"Timestamp", type datetime}, {"Action", type text}, {"Role", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Action]), "Action", "Timestamp"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Full Name", "Joined", "left", "Email", "Role"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Email", "Participant ID"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Joined", "Joined - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Joined - Copy", "Joined Time"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Full Name", "Joined", "Joined Time", "left", "Participant ID", "Role"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Joined Time", type time}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "left", "left - Copy"),
    #"Reordered Columns2" = Table.ReorderColumns(#"Duplicated Column1",{"Full Name", "Joined", "Joined Time", "left", "left - Copy", "Participant ID", "Role"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"left - Copy", "Left Time"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Left Time", type time}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each [Left Time] - [Joined Time], type duration),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Duration"}}),
    #"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns3",{"Full Name", "Joined", "Joined Time", "left", "Duration", "Left Time", "Participant ID", "Role"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns3",{"Left Time", "Joined Time"})
in
    #"Removed Columns"

Thank you so much for all your support, i am able to achieve the result, however in few steps i am geting error, attached sreen shot where the error pops up

also note there are chances where only joined would be available in the report

Thank you again for all your support

 

[VBA learner ITG]

Thank you so much for all your support, i am able to achieve the result, however in few steps i am geting error, attached sreen shot where the error pops up

also note there are chances where only joined would be available in the report

Thank you again for all your support

The error on some of the rows are due to the cell value of those dates. You will need to diagnose what the issue on the error cells are.

 

[Satheesh9012]

The error on some of the rows are due to the cell value of those dates. You will need to diagnose what the issue on the error cells are.

Is there way to do that, i got initial steps right and i see the report is standard all the date and time is the same format, can you please help with it

 

[Satheesh9012]

Is there way to do that, i got initial steps right and i see the report is standard all the date and time is the same format, can you please help with it

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:

• 
  
  Expression.Error: We couldn't parse the input provided as a Time value.
  Details:
  10/13/2021 12:01:56 PM
  
  Two errors which showed up while selecting, also i tried to remove more data to check if it is actly working i see this error as well

Expression.Error: We couldn't parse the input provided as a Time value.
Details:
10/13/2021 11:47:46 AM