Forum Discussion
Help Transposing
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
Super Userh82bu30 ,
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)- 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
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.