Pivot Column with Don't aggregate - Multiple Rows

[mash7432]

I currently have data with an "Activity" column that contains a combination of 'activities' and 'sub-activities'. I need activities and sub-activities in their own columns, with each row representing a single sub-activity.

Sample data:

Workstream	Phase	Activity	Activity Level	Helper
WS1	P1	A1	1	A
WS1	P1	SA1	2	A
WS1	P1	SA2	2	A
WS1	P1	A2	1	B
WS1	P1	SA3	2	B

Desired output:

Workstream	Phase	Activity	Sub-Activity
WS1	P1	A1	SA1
WS1	P1	A1	SA2
WS1	P1	A2	SA3

I tried to Pivot the Activity Level column, using Activity as the value and "don't aggregate" but I run into an error when there is more than 1 sub-activity.

Thanks for your help!

[DataInsights · Accepted solution]

@mash7432,

Try this in Power Query. Copy the code starting with ChangeType and paste into your query editor. This solution assumes that subactivities are listed below the activity to which they relate.

let
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "i45WCg82VNJRCgARjiACzFCK1UGRCQZLGWGXMsIlBZYBMZwwNRlDNQGlYgE=", 
                  BinaryEncoding.Base64
                ), 
              Compression.Deflate
            )
        ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [Workstream = _t, Phase = _t, Activity = _t, #"Activity Level" = _t, Helper = _t]
    ),
  ChangeType = Table.TransformColumnTypes(Source, {{"Activity Level", Int64.Type}}),
  AddColumn = Table.AddColumn(
      ChangeType, 
      "Activity Parent", 
      each if [Activity Level] = 2 then null else [Activity]
    ),
  FillDown = Table.FillDown(AddColumn, {"Activity Parent"}),
  FilterRows = Table.SelectRows(FillDown, each ([Activity Level] = 2)),
  ChangeType2 = Table.TransformColumnTypes(FilterRows, {{"Activity Parent", type text}}),
  RenameColumns = Table.RenameColumns(
      ChangeType2, 
      {{"Activity", "Subactivity"}, {"Activity Parent", "Activity"}}
    )
in
  RenameColumns