Splitting a Table into several when web data source doesnt cleanly separate tables

[dkerr88]

Hi, I'm using a Web Data Source to obtain fauna data (http://www.environment.gov.au/cgi-bin/sprat/public/publicthreatenedlist.pl) but their table structure doesn't cleanly split tables, it has 'headings' in some rows instead of closing the table and starting a new one. The resulting data looks like this - any idea how to split a single table into multiple ones when the delimiting 'column value' doesn't appear on each row , and the split is denoted by one of a known set of words (birds, mammals, ..). Thanks! col1 col2 col3 birds finch 10 india parrot 12 africa ..... mammals kangaroo 50 australia .... ....

[ImkeF · Accepted solution]

Please copy this code into the advanced editor and follow the steps:

let
    Source = Web.Page(Web.Contents("http://www.environment.gov.au/cgi-bin/sprat/public/publicthreatenedlist.pl")),
    Data1 = Source{1}[Data],
    CreateFilter = Table.AddColumn(Data1, "Custom", each List.AllTrue({ try (Date.From([Column3])) otherwise true, [Column3] <> "Effective"})),
    ReplaceErrors = Table.ReplaceErrorValues(CreateFilter, {{"Custom", null}}),
    AddFillDownColumn = Table.AddColumn(ReplaceErrors, "TableName", each if [Custom]=true then Text.BeforeDelimiter([Column1], "(") else null),
    FillDown = Table.FillDown(AddFillDownColumn,{"TableName"}),
    FilterOutFirstHeaders = Table.SelectRows(FillDown, each ([Custom] <> true)),
    CleanUp = Table.RemoveColumns(FilterOutFirstHeaders,{"Custom"}),
    Group = Table.Group(CleanUp, {"TableName"}, {{"SingleTable", each Table.PromoteHeaders(_), type table}}),
    Expand = Table.ExpandTableColumn(Group, "SingleTable", {"Genus, species (subspecies, population)", "Common Name", "Effective", "", "_1", "_2"}, {"Genus, species (subspecies, population)", "Common Name", "Effective", "Column1", "_1", "_2"})
in
    Expand

[dkerr88]

Thank you, that worked like a charm, this is extremely helpful, ImkeF! 🙂