Data Import Logic

[Anonymous]

I am trying to append multiple data sources to one query. Sometimes one of those sources do not have any data, and it presents errors in the appended table. Is there a way to state if there is no data in a source, do not load the source. And then within the appended query, if there is no source loaded, skip append?

Not sure if that made sense. But trying to cut out my having to come in and check monthly if there was an error in the source import and to make a temporary adjustment to the appended data table to exclude that source from the import.

Thanks,

[edhans]

Yes. The following formula will do this:

= if Table.IsEmpty(Table2) = true then Table else Table.Combine({Table, Table2})

[Anonymous]

I have 6 data sets being combined. So would I have to create a lengthy formula laying out each iteration? (i.e. If table 1 empty, then tablecombine 2,3,4,5,6, else if table 2 empty, then tablecombine 1,3,4,5,6)

Also, to that point above, how can that formula be simplified so that the else statement combines only those table that are not empty. 

[mahoneypat]

The tables in Table.Combine is a list, so you can use list functions to filter out the nulls, perhaps with this approach.

Source = Table.Combine(List.Select({Table1, Table2, ...}, each Table.IsEmpty(_) = false))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Unfortunately, those solutions did not work. So instead of trying to fix my appended data table, I decided to look for a way to fix how the output would be from each source query pulling no data. 

What I used was Table.SelectColumns(#"Promoted Headers",{"District", "County", "Highway", "Type of Work", "Let Date", "Project No.", "Control CSJ", "Est. Cost", "Contract Type"}, MissingField.UseNull)

I know that the steps after the import data were going to start filtering the data within these columns. So my solution was to basically create a selection of those columns if they existed, if there is a missing field, it would create those columns with no information present. Basically putting a blank table in the data set to filter. 

[v-kelly-msft]

Hi  @Anonymous ,

Seems that your issue is solved,right?If so,can you mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!