Hello,
I've been given a membership list to tidy up, consisting of a single column that I want to transform into a table. There is no fixed number of data points for each record, so I can't simply chop the list into every n rows. However, each record starts with a Role, then an optional Appointment, then LastName and FirstName, followed by an varying number of optional fields.
I've created tables and queries containing the options for Role and Appointment, so the logic of what I'm trying to do seems to be:
- If NewRecord =1, start a new row and insert Role into Column 1; move to Column 2;
- If Appointment = 1, insert Appointment into Column 2; else move to Column 3;
- Insert LastName into Column 3;
- Insert FirstName into Column 4;
- Continue adding data to subsequent columns until NewRecord is again 1.
I've attached a sample workbook to illustrate what I mean.
Is this feasible and have I over-complicated things? I also seem to have created a rogue first data row, but I'll sort that out later!
Many thanks in advance.
Pieter
Hi Pieter,
Here is my version of the query:
Source = Excel.CurrentWorkbook(){[Name="tblRawData"]}[Content],
Roles = List.Buffer(Excel.CurrentWorkbook(){[Name="tblRoles"]}[Content][RoleList]),
Appts = Excel.CurrentWorkbook(){[Name="tblAppts"]}[Content][ApptList],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if List.Contains(Roles,[Data]) then [Index] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
Group = Table.Group(#"Filled Down", {"Custom"}, {{"Grouped", each Table.SelectColumns(_,{"Data"}) }}),
Transform = Table.TransformColumns(Group,{{"Grouped", each Table.Transpose(if List.ContainsAny(Appts,_[Data]) then _ else Table.InsertRows(_,1,{[Data=null]})) }}),
RemovedColumns = Table.SelectColumns(Transform,{"Grouped"}),
#"Expanded Grouped" = Table.ExpandTableColumn(RemovedColumns, "Grouped", Table.ColumnNames(Table.Combine(RemovedColumns[Grouped])), Table.ColumnNames(Table.Combine(RemovedColumns[Grouped])))
in
#"Expanded Grouped"
My idea was to group rows that belongs to the same role. For that, I had to identify the rows in the source column that are Roles and assign to those rows a unique number.
I used for that an Index column, then added a custom column checking if data in that column is a role and assign the index if true, otherwise assign a null value.
If you fill down this column, the entries that belong to the same person will have the same index number.
GroupBy filled down column will give you each person in a table.
At this point, because only some records have appointments, we have to identify those that do not have one and insert a null row, in order to bring them to the same structure. For that I did a transformation in the Grouped tables column, transforming each table and adding a row where needed and transposing the table:
Transform = Table.TransformColumns(Group,{{"Grouped", each Table.Transpose(if List.ContainsAny(Appts,_[Data]) then _ else Table.InsertRows(_,1,{[Data=null]})) }}),
All we have to do now is to expand the Grouped column.
Hi Catalin,
Thanks for such an excellent solution, which is a real masterclass in so many ways.
I'd forgotten about setting up the lists in the same query, before manipulating the source data, which is very handy. It also emphasized the need to identify unique characteristics to exploit - in this case, extracting the Index when the corresponding row contains a list item (Role) and inserting a table row unless a different list item (Appointment) is present.
I'm very grateful - and learning lot!
Pieter
