Help Transposing

[h82bu30]

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!

[MasonMA]

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.goog...

[SundarRaj]

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=10475...

[AlienSx]

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. 

[MasonMA]

@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]

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.