Table Transformation - Column to rows ( Split Columns & append)

Power Query M Code Showcase
1

I have activity table . For each activity , there are 10 milestones and for each milestones there are 8 columns. The sequence of these 8 columns is slightly different.

In attached file , I have just added columns related to first 2 milestones.

I want split those columns ( 8 columns ) for each milestones and append to single table. Also I need to rename those 8 columns with generic name ( removing milestone no).
Table Transformation.xlsx (10.6 KB)

2

based on your provided sample, I think you need to pivot - not append your data.

most of the steps are pretty straight-forward,

  1. highlight Activity ID and Description - and unpivot all of the other columns
  2. use the “Split Column” button to first split your column names non-digit to digit (this means you’ll get two columns, one with the first part of your column name, the other with the second part (starting with the number)
  3. use “Split Column” again, this time digit to non-digit to isolate the number in the column name:
    image
  4. Merge together the first and third column name columns (this cleans up your column names, and gives you a number column to keep the pivoted data (spoiler for next step) in separate rows
  5. pivot the new merged column using the “Value” column from your original unpivot as the value - and be sure to select “Don’t Aggregate” from the advanced options:
    image
  6. cleanup your types and any other transformations you need to make - your output will look like this:
    image
    image1121Ă—115 6.52 KB

Solution is attached
eDNA Solution - Column to Rows.xlsx (22.5 KB)

1 Like
3

Thanks for your reply.

Actually I was trying to avoid unpivot and pivot .
My actual file have 30000 activities + 10 Milestones per activity x 8 steps per each Milestone.

The transformation with unpivot and unpivot was taking too much time to refresh.

4

Hi @prafullchavan1,

As a courtesy to members providing support in their personal time, please note these concerns from the onset. This will save time and ensure you receive a “better” response, with that said, give this a go.

let
    transformActivityMilestones = (t as table, skip as number) as table =>
        [
            lookFor = List.Buffer(
                {
                    "Percentage", 
                    "Description", 
                    "Progress", 
                    "Earned", 
                    "Planned", 
                    "Actual", 
                    "Forecast", 
                    "Primavera"
                }
            ), 
            // lookFor needs to map to the corresponding replWith value
            replWith = List.Buffer(
                {
                    "MILESTONE_LABEL_PERCENTAGE", 
                    "MILESTONE_LABEL_DESCRIPTION", 
                    "Step - Progress %", 
                    "EARNED_MAN_HOURS_PHASE", 
                    "PLANNED_DATE", 
                    "ACTUAL_DATE", 
                    "FORECAST_DATE", 
                    "PRIMAVERA_CODE"
                }
            ), 
            asCols = Table.ToColumns(t), 
            colNames = Table.ColumnNames(t), 
            nameChunks = List.Transform(
                List.Split(List.Skip(colNames, skip), 8), 
                each List.Transform( _, (x) => replWith{
                      List.PositionOf(
                          List.Transform( lookFor, 
                              (y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)
                          ), true
                      )
                  })
              ), 
            dataChunks = List.Split( List.Skip( asCols, skip), 8), 
            collectChunks = Table.Combine(
                List.Transform(
                    List.Zip({dataChunks, nameChunks}), 
                    each Table.FromColumns(
                        List.FirstN(asCols, skip) & _{0}, 
                        List.FirstN(colNames, skip) & _{1}
                    )
                )
            )
        ][collectChunks], 
    Source = EnterYourTableRefenceHere, 
    InvokedFunction = transformActivityMilestones(Source, 2)
in
    InvokedFunction

With this result

image
image1750Ă—79 6.77 KB

I hope this is helpful

3 Likes
5

Sorry and I will ensure this in Future.

1 Like
6

an elegant solution as always, thanks @Melissa!

and, as usual, I’ve learned something new - had not looked into List.Skip before this :slight_smile:

1 Like