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!

[v-pgoloju]

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

[v-pgoloju]

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

[Omid_Motamedise]

Can you share your sample file?

[v-pgoloju]

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

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