Handling errors when expanding table in power query

I wish to expand a table that contains data from an invoked column pulling data from the web.

The issue is that not every row finds a desired result on the web and returns an error. Although I don't mind the result failing for this row it causes an issue when trying to expand the table because the table relies on all rows having the same captured headers for the expansion.

Below is an image showing the errors and the result of the expansion.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Fx GetBriefProfileLink", each #"Fx GetBriefProfileLink"([CAS Number])),
    #"Expanded Fx GetBriefProfileLink" = Table.ExpandTableColumn(#"Invoked Custom Function", "Fx GetBriefProfileLink", {"Name", "Cas Number", "exported-column-briefProfileLink"}, {"Name", "Cas Number.1", "exported-column-briefProfileLink"})
in
    #"Expanded Fx GetBriefProfileLink"

Fx GetBriefProfileLink: Data Public

(CAsNumberorName as text) =>

let
    Source = Excel.Workbook(Web.Contents("https://echa.europa.eu/search-for-chemicals?p_p_id=disssimplesearch_WAR_disssearchportlet&p_p_lifecycle=2&p_p_state=normal&p_p_mode=view&p_p_resource_id=exportResults&p_p_cacheability=cacheLevelPage&_disssimplesearch_WAR_disssearchportlet_sessionCriteriaId=dissSimpleSearchSessionParam101401654440118533&_disssimplesearch_WAR_disssearchportlet_formDate=1654440118558&_disssimplesearch_WAR_disssearchportlet_sskeywordKey="&CAsNumberorName&"&_disssimplesearch_WAR_disssearchportlet_orderByCol=relevance&_disssimplesearch_WAR_disssearchportlet_orderByType=asc&_disssimplesearch_WAR_disssearchportlet_exportType=xls"))[Data]{0},
    #"Removed Top Rows" = Table.Skip(Source,2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"EC / List Number", type text}, {"Cas Number", type text}, {"Substance Information Page", type text}, {"exported-column-briefProfileLink", type text}, {"exported-column-obligationsLink", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "EC / List Number", "Cas Number", "exported-column-briefProfileLink"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",1),
    #"Removed Other Columns1" = Table.SelectColumns(#"Kept First Rows",{"Name", "Cas Number", "exported-column-briefProfileLink"})
in
    #"Removed Other Columns1"

Sample Data:

No. CAS Number  Chemical name
43  3380-30-1   5-chloro-2-(4-chlorphenoxy)phenol
44  03228-02-2  4-isopropyl-m-cresol
45  89-83-8 Thymol
46  60207-90-1  Propiconazole
47  5395-50-6   Tetrahydro-1,3,4,6-tetrakis(hydroxymethyl)imidazo[4,5-d]imidazole-2,5(1H,3H)-dione
48  15630-89-4  Sodium percarbonate
49  027176-87-0 Dodecylbenzenesulfonic acid
50  001344-09-8 Sodium silicate

It appears to be a glitch in PQ looking online however I am wondering if there are any workarounds.

My desired output is simply the same expansion but rows with errors just appear empty in the expanded section.

Solution

You just replace errors with null before your expansion.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}, {"Column1", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Chemical name", "Column1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Chemical name"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each #"Fx GetBriefProfileLink"([CAS Number])),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Replaced Errors", "Custom", {"Name", "Cas Number", "exported-column-briefProfileLink"}, {"Custom.Name", "Custom.Cas Number", "Custom.exported-column-briefProfileLink"})
in
    #"Expanded Custom"