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 looks like this;

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

 

Thanks

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

12 Replies