Help Transposing

Hello,

The data that I receive has recurring rows under a date sub-heading, this pattern repeats every 9 rows (9th row is a blank and can be removed). I'm wondering how I can take the recurring date row and transpose into a column to improve the data structure of the database, then compress all of the Persons per Job data. So the new headings would be Person, DateRange, Job1, Job2, etc.

Original

Desire (if there's a better structure, let me know)

I'm new to PowerQuery and loving the opportunities, however all the example I work through have much less in terms of indexing, modulo, pivoting, etc. Thanks for any guidance on ways to solve this problem. Thank you!

Tips and Tricks

SundarRaj
1 year ago
Hi h82bu30 , another PQ solution you could have a look at. I'll attach the images of the output and M Code used. Thanks!
Here's the code:
let
Source = #table(
{"ColP", "Col1", "Col2", "Col3", "Col4", "Col5", "Col6", "Col7"},
{
{"01/01/2025 - 01/31/2025", null, null, null, null, null, null, null},
{null, "Job 1", "Job 2", "Job 3", "Job 4", "Job 5", "Job 6", "Job 7"},
{"Person A", 23, null, null, null, null, null, null},
{"Person B", 15, null, null, null, null, 4, 2},
{"Person C", null, null, null, null, 4, null, null},
{"Person D", 18, null, null, null, null, null, null},
{"Person E", null, null, null, 19, 2, null, null},
{"Person F", null, null, null, null, null, null, null},
{"02/01/2025 - 02/28/2025", null, null, null, null, null, null, null},
{null, "Job 1", "Job 2", "Job 3", "Job 4", "Job 5", "Job 6", "Job 7"},
{"Person A", null, null, 17, null, 7, null, null},
{"Person B", null, 11, 4, null, null, null, null},
{"Person C", null, null, 7, null, null, 2, null},
{"Person D", null, null, null, null, null, null, null},
{"Person E", null, null, null, null, null, null, 4},
{"Person F", null, null, null, null, null, null, null},
{"03/01/2025 - 03/31/2025", null, null, null, null, null, null, null},
{null, "Job 1", "Job 2", "Job 3", "Job 4", "Job 5", "Job 6", "Job 7"},
{"Person A", null, null, null, null, null, null, null},
{"Person B", null, 20, null, null, null, null, null},
{"Person C", null, 13, null, null, null, null, null},
{"Person D", null, 17, null, null, null, 4, null},
{"Person E", null, null, null, 23, null, null, null},
{"Person F", null, null, null, null, null, null, null}
}
),
Dates = Table.SelectRows(Source, each Text.Contains([ColP], "-"))[ColP],
Count = List.Count(
List.Distinct(Table.SelectRows(Source, each Text.Contains([ColP], "Person"))[ColP])
),
Tables = Table.Split(Table.SelectRows(Source, each Text.Contains([ColP], "Person")), Count),
ColNames = {"Person"} & List.RemoveNulls(Record.ToList(Source{1})),
Rename = List.Transform(
Tables,
each Table.RenameColumns(_, List.Zip({Table.ColumnNames(_), ColNames}))
),
Convert = Table.FromList(Rename, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
DateAdded = Table.TransformColumns(
Table.AddIndexColumn(Convert, "Date", 0, 1),
{"Date", each Dates{_}}
),
Expand = Table.ExpandTableColumn(DateAdded, "Column1", ColNames, ColNames),
Reorder = Table.ReorderColumns(Expand, ColNames)
in
Reorder

I'll attach the file link here as well:
https://docs.google.com/spreadsheets/d/1z1lA0Uqa_pAjagrLyi_ySN5ZpdGuOppg/edit?usp=sharing&ouid=104752674875039603034&rtpof=true&sd=true

9 Replies

MasonMA
Super User
1 year ago
h82bu30 ,
Hi, I'd suggest you try below steps
1. Remove Blank Rows by using 'Remove Blank Rows' to eliminate the 9th row in each block.
2. Add an Index Column. Go to 'Add Column' , 'Index Column'→ Start from 0.
3. Create a Grouping Key. Add a custom column to group every 8 rows (since the 9th is blank and removed
GroupID = Number.IntegerDivide([Index], 8)
4. Fill Down the 'Date':
Filter rows where the 'Job' column is not null and contains a date range.
Use 'Fill Down' on the 'Date' column to propagate the date range to all rows in the group.
5. Remove Unnecessary Columns:
If the original Date column is now redundant, remove it and keep the filled-down version.
6. Group and Aggregate 'Jobs' (Optional), If you want to compress multiple jobs into a single row per person:
Group by Person and DateRange
Use 'Text.Combine' on the Job column with a delimiter like a space or comma.
Hopefully it helps
- h82bu30
  New Member
  1 year ago
  Appreciate the speedy response. Could you clarify step 4?
  
  A problem that I'm having is the original data has mixed data. It goes 'date range', blank, Person A, Person B, etc.
  
  It seems I'll need a way to split the 'date range' from the different people (Person A, Person B, etc). I'm not sure how to utilize the Grouping Key/Group ID to do that. Thanks again for the speedy eval and recommendation.

AlienSx

Super User

1 year ago

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content], 
    group = Table.Group(
        Source, 
        "Column1", 
        {"x", (x) => Table.RemoveLastN(Table.Skip(x, 2), 1)}, 
        GroupKind.Local, 
        (s, c) => Number.From(Text.Contains(c ?? "", "/"))
    ), 
    tbl = Table.FromPartitions("Date", List.Zip(Table.ToColumns(group))),
    rename = Table.RenameColumns(
        tbl, 
        List.Zip(
            {
                List.RemoveLastN(Table.ColumnNames(tbl), 1), 
                {"Person"} & List.Skip(Record.ToList(Source{1}))
            }
        )
    )
in
    rename

then re-order columns and sort.

SundarRaj
Super User
1 year ago
Hi h82bu30 , another PQ solution you could have a look at. I'll attach the images of the output and M Code used. Thanks!
Here's the code:
let
Source = #table(
{"ColP", "Col1", "Col2", "Col3", "Col4", "Col5", "Col6", "Col7"},
{
{"01/01/2025 - 01/31/2025", null, null, null, null, null, null, null},
{null, "Job 1", "Job 2", "Job 3", "Job 4", "Job 5", "Job 6", "Job 7"},
{"Person A", 23, null, null, null, null, null, null},
{"Person B", 15, null, null, null, null, 4, 2},
{"Person C", null, null, null, null, 4, null, null},
{"Person D", 18, null, null, null, null, null, null},
{"Person E", null, null, null, 19, 2, null, null},
{"Person F", null, null, null, null, null, null, null},
{"02/01/2025 - 02/28/2025", null, null, null, null, null, null, null},
{null, "Job 1", "Job 2", "Job 3", "Job 4", "Job 5", "Job 6", "Job 7"},
{"Person A", null, null, 17, null, 7, null, null},
{"Person B", null, 11, 4, null, null, null, null},
{"Person C", null, null, 7, null, null, 2, null},
{"Person D", null, null, null, null, null, null, null},
{"Person E", null, null, null, null, null, null, 4},
{"Person F", null, null, null, null, null, null, null},
{"03/01/2025 - 03/31/2025", null, null, null, null, null, null, null},
{null, "Job 1", "Job 2", "Job 3", "Job 4", "Job 5", "Job 6", "Job 7"},
{"Person A", null, null, null, null, null, null, null},
{"Person B", null, 20, null, null, null, null, null},
{"Person C", null, 13, null, null, null, null, null},
{"Person D", null, 17, null, null, null, 4, null},
{"Person E", null, null, null, 23, null, null, null},
{"Person F", null, null, null, null, null, null, null}
}
),
Dates = Table.SelectRows(Source, each Text.Contains([ColP], "-"))[ColP],
Count = List.Count(
List.Distinct(Table.SelectRows(Source, each Text.Contains([ColP], "Person"))[ColP])
),
Tables = Table.Split(Table.SelectRows(Source, each Text.Contains([ColP], "Person")), Count),
ColNames = {"Person"} & List.RemoveNulls(Record.ToList(Source{1})),
Rename = List.Transform(
Tables,
each Table.RenameColumns(_, List.Zip({Table.ColumnNames(_), ColNames}))
),
Convert = Table.FromList(Rename, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
DateAdded = Table.TransformColumns(
Table.AddIndexColumn(Convert, "Date", 0, 1),
{"Date", each Dates{_}}
),
Expand = Table.ExpandTableColumn(DateAdded, "Column1", ColNames, ColNames),
Reorder = Table.ReorderColumns(Expand, ColNames)
in
Reorder

I'll attach the file link here as well:
https://docs.google.com/spreadsheets/d/1z1lA0Uqa_pAjagrLyi_ySN5ZpdGuOppg/edit?usp=sharing&ouid=104752674875039603034&rtpof=true&sd=true
MasonMA
Super User
1 year ago
Hi h82bu30
I would leverage UI in Power Query as much as i can and only code in M wherever it is not possible with UI.
For the detailed steps you can refer to the attached file.
fyi, the index column and flag column are not essential for your transformation but better to keep them for debugging purposes later.
thanks
https://drive.google.com/file/d/1Y-9SCbYdM38naAlA9SQQ1odpQc1caB14/view?usp=sharinghttps://drive.google.com/file/d/1Y-9SCbYdM38naAlA9SQQ1odpQc1caB14/view?usp=drive_link
Anonymous
Not applicable
1 year ago
Hi h82bu30,

Just following up to see if the solution helped you reshape the repeating 9 row pattern transposing the recurring date rows into a column and restructuring the data with Person, DateRange, and Job details. Let me know if you need any further assistance with compressing the Person Job data.

Best regards,
Prasanna Kumar
Omid_Motamedise
Super User
1 year ago
Can you share your sample file?
Anonymous
Not applicable
1 year ago
Hi h82bu30,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar
Anonymous
Not applicable
1 year ago
Hi h82bu30

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Omid_Motamedise , MasonMA , SundarRaj and AlienSx for their prompt and helpful responses.

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

Forum Discussion

Help Transposing

9 Replies

Related content

Transpose data

Transpose Title Row ONLY

Pivot/ transpose

Transpose/ Transform Rows to Columns

transpose