Data from 2 columns based on 3rd column

[markdem]

Hello,
I have no idea how to word this question to google so sorry if it is something that has been answered before.

I have a simple time clock table I would like to report on. Table of events looks like this;

How would I go about making a new table that looks like this?

Thanks

[markdem]

I now have one more issue. The "User” column needs to be joint with data from a different table.

If I try to merge the data with “= Table.NestedJoin(dbo_Events, {"userID"}, Users, {"userID"}, "Users", JoinKind.LeftOuter)” I get a error about not been able to have nested data in a pivot.

How can I get around this?

Thanks

[v-lili6-msft]

hi  @markdem 

For your case, you could try this way:

Step1:

The most important step is you need to add a date column for the datetime column as a bin.

You could selected datetime column and click Add column->Date->Date only as below to get it.

Step2:

Then selected clockin/out column and use Pivot it as below:

https://radacad.com/pivot-and-unpivot-with-power-bi

Step3:

Remove unless columns and get your expected output:

here is sample pbix file, please try it.

Regards,

lin

[markdem]

Sorry for the hugely delayed reply..
I have tried the instructions above but it only seems to be working when the clockin an clockout times are the same minute...

Here is my data now. Anyway I can get rid of all the nulls?

[v-lili6-msft]

hi  @markdem 

This means that there is no ClockIN or ClockOUT for that date (there is only one of ClockIN or ClockOUT for that date), 

So you could filter null value for ClockIN and ClockOUT column

Regards,

Lin

[markdem]

But there is a event for both a IN and OUT.
If we have a look at user 157 (first column), I generated a clockIN event at 0800 and OUT event at 1700 on the dates between 21/06 and 28/06.

Thanks

[Ashish_Mathur]

Hi,

Share sample data in a format that can be pasted in an MS Excel file.

[markdem]

Here is some sample data for 2 users.

142	MELCLOCK01	ClockIN	2020-06-27T07:04:27.8900000	0
142	MELCLOCK01	ClockOUT	2020-06-27T15:25:08.1500000	501
156	MELCLOCK01	ClockIN	2020-06-28T08:56:39.2100000	0
156	MELCLOCK01	ClockOUT	2020-06-28T18:04:39.7670000	548
156	MELCLOCK01	ClockIN	2020-06-29T10:58:03.8470000	0
156	MELCLOCK01	ClockOUT	2020-06-29T21:53:34.5630000	655
142	MELCLOCK01	ClockIN	2020-06-30T07:23:19.2800000	0
156	MELCLOCK01	ClockIN	2020-07-01T07:28:10.2430000	0
142	MELCLOCK01	ClockOUT	2020-07-01T07:36:55.0800000	720
156	MELCLOCK01	ClockOUT	2020-07-01T15:53:45.5030000	506
142	MELCLOCK01	ClockIN	2020-07-02T07:24:49.3330000	0
156	MELCLOCK01	ClockIN	2020-07-02T08:48:37.7700000	0
156	MELCLOCK01	ClockOUT	2020-07-02T18:24:52.4230000	576
142	MELCLOCK01	ClockOUT	2020-07-02T18:38:04.1130000	673
142	MELCLOCK01	ClockIN	2020-07-03T06:32:11.5700000	0
142	MELCLOCK01	ClockOUT	2020-07-03T16:14:06.8930000	582
142	MELCLOCK01	ClockIN	2020-07-04T06:57:38.5270000	0
142	MELCLOCK01	ClockOUT	2020-07-04T17:49:35.2030000	260
156	MELCLOCK01	ClockIN	2020-07-05T07:29:53.5600000	0
156	MELCLOCK01	ClockOUT	2020-07-05T18:29:10.5030000	659
156	MELCLOCK01	ClockIN	2020-07-06T12:34:02.2470000	0
156	MELCLOCK01	ClockOUT	2020-07-06T22:25:08.6530000	591
142	MELCLOCK01	ClockIN	2020-07-07T07:36:30.4800000	0
142	MELCLOCK01	ClockOUT	2020-07-07T16:53:02.1900000	316
142	MELCLOCK01	ClockIN	2020-07-08T06:49:16.0670000	0
156	MELCLOCK01	ClockIN	2020-07-08T08:42:06.1670000	0
156	MELCLOCK01	ClockOUT	2020-07-08T16:35:09.8800000	473
142	MELCLOCK01	ClockOUT	2020-07-08T18:17:12.4070000	272
142	MELCLOCK01	ClockIN	2020-07-09T09:09:32.5300000	0
142	MELCLOCK01	ClockOUT	2020-07-10T06:31:21.3170000	721
142	MELCLOCK01	ClockIN	2020-07-11T08:09:54.7530000	0
142	MELCLOCK01	ClockOUT	2020-07-11T17:23:42.5100000	132
156	MELCLOCK01	ClockIN	2020-07-12T08:49:25.5630000	0
156	MELCLOCK01	ClockOUT	2020-07-12T16:57:34.0070000	488
156	MELCLOCK01	ClockIN	2020-07-13T08:15:41.3830000	0
156	MELCLOCK01	ClockOUT	2020-07-13T16:24:46.9600000	489

Thanks

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVW7bhsxEPwX1dZin1ySrZAiiB03TGW4SpkA+f8uS0qibTl2jneVDicMZuex+/R0IOXD3eHhy/3p/vH0DSleTr///Pz19Xv8YmQ8YjqyN/SKWtkhF+xPfMXD893HAI8/2lsEsspWMQPZFcHi7wPD0gYSuWGulqoUYLoh8QHADYncKPcxAsGTX0loXiBRGmG1QBHI6ntIlMZUTaooWJILQjL7VM03JAS7HSyVQom8TYlXAH5EGgC5EgKrLPt5RZBUzQAnBeftSgyMyEQooQaGMjORtioREDwG0aoFRGSHEtxDpbmKg/t6qAZChCooGIPynMI/n+JfGNKzCUQzEy4LSkjDqAZXIrDbQbaRkEapklZMUfI5SOYFEtpJWAQjg7HvIaGNPNysYsAzE5xWLLWRiRLJioLtstSGpaXX4yWYycoCidSIo+IVORq2vicGAvNlYyabdhRasMMvJRUEvd0T2+zwnomoaIxBc/ELrVQ090yEo5QA0zYl3gFERbnnkjYC3EyR+xQSUhbIUwf9T73eY8S+9EpRc7yyYF9pR2lYgkO0FM6GrtpBOEpOlQmEriScFzJB1NUMEqbgtmf1BwKN66MxxrzDJLzdUjpv3RLxfnUDVywNBDpvGgWcdmjefMoDQjqJuEAaYuZdJMbG7NcnQZmbRnPsiee/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"User Name" = _t, Type = _t, #"Date/Time" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"User Name", type text}, {"Type", type text}, {"Date/Time", type datetime}, {"Value", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date/Time", "Date/Time - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date/Time - Copy", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Date/Time"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Date/Time - Copy", "Date"}})
in
    #"Renamed Columns"

Hope this helps.

[v-lili6-msft · Accepted solution]

hi  @markdem 

For your problem, it is because you have other columns that needs to be aggregated before pivot.

So adjust it as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVW7bhsxEPwX1dZin1ySrZAiiB03TGW4SpkA+f8uS0qibTl2jneVDicMZuex+/R0IOXD3eHhy/3p/vH0DSleTr///Pz19Xv8YmQ8YjqyN/SKWtkhF+xPfMXD893HAI8/2lsEsspWMQPZFcHi7wPD0gYSuWGulqoUYLoh8QHADYncKPcxAsGTX0loXiBRGmG1QBHI6ntIlMZUTaooWJILQjL7VM03JAS7HSyVQom8TYlXAH5EGgC5EgKrLPt5RZBUzQAnBeftSgyMyEQooQaGMjORtioREDwG0aoFRGSHEtxDpbmKg/t6qAZChCooGIPynMI/n+JfGNKzCUQzEy4LSkjDqAZXIrDbQbaRkEapklZMUfI5SOYFEtpJWAQjg7HvIaGNPNysYsAzE5xWLLWRiRLJioLtstSGpaXX4yWYycoCidSIo+IVORq2vicGAvNlYyabdhRasMMvJRUEvd0T2+zwnomoaIxBc/ELrVQ090yEo5QA0zYl3gFERbnnkjYC3EyR+xQSUhbIUwf9T73eY8S+9EpRc7yyYF9pR2lYgkO0FM6GrtpBOEpOlQmEriScFzJB1NUMEqbgtmf1BwKN66MxxrzDJLzdUjpv3RLxfnUDVywNBDpvGgWcdmjefMoDQjqJuEAaYuZdJMbG7NcnQZmbRnPsiee/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, UserName = _t, Type = _t, Datetime = _t, OtherColumn1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", Int64.Type}, {"UserName", type text}, {"Type", type text}, {"Datetime", type datetime}, {"OtherColumn1", Int64.Type}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Datetime]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"UserID", "UserName", "Date"}, {{"New Othercolumn1", each List.Sum([OtherColumn1]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"UserID", "UserName", "Date"}, #"Inserted Date", {"UserID", "UserName", "Date"}, "Grouped Rows", JoinKind.Inner),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Type", "Datetime"}, {"Type", "Datetime"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Grouped Rows", List.Distinct(#"Expanded Grouped Rows"[Type]), "Type", "Datetime")
in
    #"Pivoted Column"

here is sample pbix file, please try it.

Regards,

Lin

[markdem]

Perfect. I did not know abou this.

Thanks

[amitchandak]

@markdem , refer pivot

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

@markdem ,

You could achieve this by unpoviting your tabel by clockin/out column.

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...