Thanks in advance! I have a query pulling taxonomies from the NPPES Npi lookup database through and invoked function. The issue is when I expand the column to display the specialty of the NPI referenced from my table, it results in an error (provider has been de-activated in the NPI registry) and stops the rest of the query from working. I would be fine if it continued to display rows with error or "deactivated", but it just stops altogether. Can anyone help me here?
let
Source = Access.Database(File.Contents("P:\aaaaaa\folderstructurefile.accdb"), [CreateNavigationProperties=true]),
#"ACCESSQUERY" = Source{[Schema="",Item="ACCESSQUERY"]}[Data],
#"Invoked Custom Function" = Table.AddColumn(#"ACCESSQUERY”, "SPECIALIST", each NPIFUN([TEXTNPI])),
#"Expanded SPECIALIST" = Table.ExpandTableColumn(#"Invoked Custom Function", "SPECIALIST", {"Value"}, {"SPECIALIST.Value"}),
Value = #"Expanded SPECIALIST"[Value]
in
Value
This is from advanced editor. I just want all records to show, whether or not there is information associated with it.
Also, here is the function
let
Source = (#"NPI LOOKUP" as text) => let
Source = try Json.Document(Web.Contents("https://npiregistry.cms.hhs.gov/api/?version=2.1&number=" & #"NPI LOOKUP"))otherwise null,
results = Source[results],
results1 = results{0},
taxonomies = results1[taxonomies],
taxonomies1 = taxonomies{0},
#"Converted to Table" = Record.ToTable(taxonomies1),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "desc")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"})
in
#"Removed Columns"
in
Source
