PowerBI pivot multiple rows for each ID into columns

Question

In Power BI I have data that looks like this:

ID	DateTime	Name
gfdh-65gfhd-654h	2021-12-01 21:00:00 am	George
gfdh-65gfhd-654h	2021-12-02 21:00:00 am	Paul
gfdh-65gfhd-654h	2021-12-03 21:00:00 am	Tom
nbvc-65khgf-764g	2021-12-02 21:00:00 am	George
nbvc-65khgf-764g	2021-12-03 21:00:00 am	Tom

Where the number of rows per ID is variable and I want it to look like this:

ID	DateTime	Name	DateTime1	Name1	DateTime2	Name2
gfdh-65gfhd-654h	2021-12-01 21:00:00 am	George	2021-12-02 21:00:00 am	Paul	2021-12-03 21:00:00 am	Tom
nbvc-65khgf-764g	2021-12-02 21:00:00 am	George	2021-12-03 21:00:00 am	Tom

where the number of columns per ID correlates to the number of rows per ID in the original table.

Can this be done in Power M? If so, how?

Answer 1

Here's one method - creating a partition per ID, then sorting, unpivoting Dates / Names, then rejoining and reordering.

This will scale for variable, unspecified number of entries per ID, and will transform and reorder appropriately.

let
    fnPartition = (MyPartition as table) =>
    let
        #"fnP Sorted Rows" = Table.Sort(MyPartition,{{"DateTime", Order.Ascending}}),
        #"fnP Added Index" = Table.AddIndexColumn(#"fnP Sorted Rows", "Index", 0, 1, Int64.Type),
        #"fnP Combined Data" = Table.Combine({fnUnpivot(#"fnP Added Index", "DateTime", 0), fnUnpivot(#"fnP Added Index", "Name", 1)}),
        #"fnP Transposed Back" = Table.Transpose(#"fnP Combined Data"),
        #"fnP Promoted Headers" = Table.PromoteHeaders(#"fnP Transposed Back", [PromoteAllScalars=true])
    in
        #"fnP Promoted Headers",

    fnUnpivot = (MyTable as table, MyHeader as text, MySkipRows as number) =>
    let
        #"fnU Added Header" = Table.AddColumn(MyTable, "Header", each MyHeader & Text.From([Index]), type text),
        #"fnU Selected Columns" = Table.SelectColumns(#"fnU Added Header",{"ID", MyHeader, "Header"}),
        #"fnU Pivoted" = Table.Pivot(#"fnU Selected Columns", List.Distinct(#"fnU Selected Columns"[Header]), "Header", MyHeader),
        #"fnU Demoted Headers" = Table.DemoteHeaders(#"fnU Pivoted"),
        #"fnU Transposed" = Table.Transpose(#"fnU Demoted Headers"),
        #"fnU Removed Top Rows" = Table.Skip(#"fnU Transposed",MySkipRows)
    in
        #"fnU Removed Top Rows",

    fnSortTransform = (value) => Text.End(value, 1) & Text.Start(value, 1),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk9LydA1M01Py0gBUiYZSjpKRgZGhrqGRroGhgpGhlYGBkAEFHVPzS9KT1WK1cGvxwhZT0BiaQ5BHcbIOkLyc8Ea8pLKkoEqszPS03TNzUzScVqB5Cx8erBags9VJsga/HNyKpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DateTime = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}),
    #"Created Partitions" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each fnPartition(_), type table}}),
    #"Combined Partitions" = Table.Combine(#"Created Partitions"[Data]),
    #"Reordered Columns" = Table.ReorderColumns(#"Combined Partitions",List.Combine({List.FirstN(Table.ColumnNames(#"Combined Partitions"),1), List.Sort(List.Skip(Table.ColumnNames(#"Combined Partitions"),1), (a, b) => Value.Compare(fnSortTransform(a), fnSortTransform(b)))})),
    #"Changed Type Text" = Table.TransformColumnTypes(#"Reordered Columns", List.Transform(List.Select(Table.ColumnNames(#"Reordered Columns"),each not Text.Contains(_,"Date")), each {_, type text})),
    #"Changed Type DateTime" = Table.TransformColumnTypes(#"Changed Type Text", List.Transform(List.Select(Table.ColumnNames(#"Reordered Columns"),each Text.Contains(_,"Date")), each {_, type datetime}))
in
    #"Changed Type DateTime"

This returns:

Answer 2

Can you please try this

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WSk9LydA1M01Py0gBUiYZSjpKRgZGhrqGRroGhgpGhlYGBkCkEOALlHBPzS9KT1WK1cGvzQhNW0BiaQ5BTcZomkLyc8F68pLKkoGKszPS03TNzUzS8VmE5D582rBaFQsA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, DateTime = _t, Name = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"ID", type text}, {"DateTime", type datetime}, {"Name", type text}}
  ),
  #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", {{"DateTime", type text}}),
  #"Grouped Rows" = Table.Group(
    #"Changed Type1",
    {"ID"},
    {
      {
        "ad",
        each _,
        type table [ID = nullable text, DateTime = nullable text, Name = nullable text]
      }
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows",
    "DateTime",
    each
      let
        x = [ad],
        y = x[DateTime],
        z = Text.Combine(y, ",")
      in
        z
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Name",
    each
      let
        x = [ad],
        y = x[Name],
        z = Text.Combine(y, ",")
      in
        z
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom1", {"ad"}),
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Removed Columns",
    "DateTime",
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
    {"DateTime.1", "DateTime.2", "DateTime.3"}
  ),
  #"Changed Type2" = Table.TransformColumnTypes(
    #"Split Column by Delimiter",
    {{"DateTime.1", type datetime}, {"DateTime.2", type datetime}, {"DateTime.3", type datetime}}
  ),
  #"Split Column by Delimiter1" = Table.SplitColumn(
    #"Changed Type2",
    "Name",
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
    {"Name.1", "Name.2", "Name.3"}
  ),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Split Column by Delimiter1",
    {{"Name.1", type text}, {"Name.2", type text}, {"Name.3", type text}}
  ),
  #"Reordered Columns" = Table.ReorderColumns(
    #"Changed Type3",
    {"ID", "DateTime.1", "Name.1", "DateTime.2", "Name.2", "DateTime.3", "Name.3"}
  )
in
  #"Reordered Columns"

The above produces the following