Forum Discussion
Searching a complex set of data
Hi Greg_Taylor
Attached is a Get & Transform aka Power Query option:
// Table
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
TableSplit = Table.Split(Source, 6),
ToTable = Table.FromList(TableSplit,
Splitter.SplitByNothing(), {"RECORD"}, null, ExtraValues.Error
),
SortBy = Table.AddColumn(ToTable, "SortBy", each
Table.First( Table.Skip([RECORD], 2) )[N]
),
SortedRows = Table.Sort(SortBy, {{"SortBy", Order.Ascending}}),
RemovedSortBy = Table.RemoveColumns(SortedRows, {"SortBy"}),
CombinedRecords = Table.Combine(RemovedSortBy[RECORD])
in
CombinedRecords
- Put your data in Table sheet 'Input'
- Switch to sheet 'Output'
- Right-click in a cell below A-O > Refresh
Interesting formula! I don't think I could have created that. Are steps such as
Table.Split(Source, 6)available from the UI or do you have to know the commands to enter them? At least reading your query caused me to dip into the joys of 'selection' and 'projection' once more with a little more sense of purpose.
One thing that occurs to me is that, if Excel 365 is now Turing complete, I should be able to replicate the action of any PQ step using a suitable defined Lambda function. In this case, a list of tables could be replicated as an array of thunks. Unfortunately replicating the functionality of Table.Combine, though possible, is something of a nightmare. The sooner Excel allows lists of arrays the better!
Talking of nightmares, we should persuade Greg_Taylor to post a sample of his workbook here. The data blocks appear to serve the role of a sequence of forms, separated by blank rows and any field can display across several blank columns before the next set of values are encountered. The set task was to sort the 'forms' but I was sorely tempted to normalise the data format instead.
Thank you Peter and L z.... I'm only an egg with Excel, so I Private Messaged Peter to be less exposed in my ignorance, but am happy to move back to a more public area, and to further the education of those who can follow, with my DEMO file (once I figure out how to do that....).
I am, as I said, very appreciative of all help. I have to admit 2 things though: re: Peter's solution, that I am still confused about how to use these wonderful tools you have giving me: it seems to be an elegant scalpel but I seem to be only equipped to use it as a hammer.... it is good to feel humble, I guess.... and second, I can't count well, as the data file looks different to me know, that it has "7" rows (plus a following hidden row, just to throw a curve in.). So I am reluctant to try L z's (much more fit to me, cause I THINK it is meant to be a Copy and Paste solution, much more in keeping with my skill set....). Anyway, here is the DEMO file...- BTW, I am happy if the output is in a single row , especially if the columns are labeled. Even if they are not, it will be easy, I think, to figure the column names out "manually."
