Forum Discussion

markdem's avatar
markdem
Icon for Helper I rankHelper I
6 years ago
Solved

Data from 2 columns based on 3rd column

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 lo...
Need Help
Tutorial Requests
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

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

markdem's avatar
markdem
Icon for Helper I rankHelper I
6 years ago

Here is some sample data for 2 users.

142MELCLOCK01ClockIN2020-06-27T07:04:27.89000000
142MELCLOCK01ClockOUT2020-06-27T15:25:08.1500000501
156MELCLOCK01ClockIN2020-06-28T08:56:39.21000000
156MELCLOCK01ClockOUT2020-06-28T18:04:39.7670000548
156MELCLOCK01ClockIN2020-06-29T10:58:03.84700000
156MELCLOCK01ClockOUT2020-06-29T21:53:34.5630000655
142MELCLOCK01ClockIN2020-06-30T07:23:19.28000000
156MELCLOCK01ClockIN2020-07-01T07:28:10.24300000
142MELCLOCK01ClockOUT2020-07-01T07:36:55.0800000720
156MELCLOCK01ClockOUT2020-07-01T15:53:45.5030000506
142MELCLOCK01ClockIN2020-07-02T07:24:49.33300000
156MELCLOCK01ClockIN2020-07-02T08:48:37.77000000
156MELCLOCK01ClockOUT2020-07-02T18:24:52.4230000576
142MELCLOCK01ClockOUT2020-07-02T18:38:04.1130000673
142MELCLOCK01ClockIN2020-07-03T06:32:11.57000000
142MELCLOCK01ClockOUT2020-07-03T16:14:06.8930000582
142MELCLOCK01ClockIN2020-07-04T06:57:38.52700000
142MELCLOCK01ClockOUT2020-07-04T17:49:35.2030000260
156MELCLOCK01ClockIN2020-07-05T07:29:53.56000000
156MELCLOCK01ClockOUT2020-07-05T18:29:10.5030000659
156MELCLOCK01ClockIN2020-07-06T12:34:02.24700000
156MELCLOCK01ClockOUT2020-07-06T22:25:08.6530000591
142MELCLOCK01ClockIN2020-07-07T07:36:30.48000000
142MELCLOCK01ClockOUT2020-07-07T16:53:02.1900000316
142MELCLOCK01ClockIN2020-07-08T06:49:16.06700000
156MELCLOCK01ClockIN2020-07-08T08:42:06.16700000
156MELCLOCK01ClockOUT2020-07-08T16:35:09.8800000473
142MELCLOCK01ClockOUT2020-07-08T18:17:12.4070000272
142MELCLOCK01ClockIN2020-07-09T09:09:32.53000000
142MELCLOCK01ClockOUT2020-07-10T06:31:21.3170000721
142MELCLOCK01ClockIN2020-07-11T08:09:54.75300000
142MELCLOCK01ClockOUT2020-07-11T17:23:42.5100000132
156MELCLOCK01ClockIN2020-07-12T08:49:25.56300000
156MELCLOCK01ClockOUT2020-07-12T16:57:34.0070000488
156MELCLOCK01ClockIN2020-07-13T08:15:41.38300000
156MELCLOCK01ClockOUT2020-07-13T16:24:46.9600000489

 

Thanks

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      markdem
      Icon for Helper I rankHelper I
      6 years ago

      Perfect. I did not know abou this.

      Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.