Append - data source error

[babuInba]

Hi Experts,

I would like to append multiple data source, but when one data source not found, i'm unable to append other tables.

How to handle this error, since few data might not come on daily basis.

I'm getting 25 sheets in a spreadsheet, i'm transforming each sheet seperately and append at the end.

I'm considering sheet name as table name, some sheets not come on daily basis, so im getting data source error.

Suggest me better solution.

Thanks in Advance.

[mahoneypat · Accepted solution]

If each of your sheets have the same data structure (columns), you should have a single query that combines them all instead of 25 separate queries.

Combine All Sheets in a Workbook with Power Query - Excelerator BI

Pat

[v-jingzhang · Accepted solution]

Hi @babuInba 

The video Pat shares is awesome. However, if your sheets don't have the same structure thus you have to transform them in separate queries before appending, you could refer to my practice.

Assuem I have 3 queries to be append, among them Sheet3 may not exist sometimes. Its original M codes before appending are as follows. It has 7 columns.

let
    Source = Excel.Workbook(File.Contents("......\Test.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sheet", Int64.Type}, {"Customer", type text}, {"Category", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Price", Int64.Type}, {"Amount", Int64.Type}})
in
    #"Changed Type"

When this sheet doesn't exist in the workbook, it will raise error. So we need to deal with the error. You can refer to M Language Error Handling for more instruction. Here what I do is to transform the codes into below one. Add parentheses to wrap original codes and add try before them. At the end, add otherwise and return an empty table. You could also hard code column headers to return an empty table with only column headers. 

try
(
    let
        Source = Excel.Workbook(File.Contents("......\Test.xlsx"), null, true),
        Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sheet", Int64.Type}, {"Customer", type text}, {"Category", type text}, {"Product", type text}, {"Quantity", Int64.Type}, {"Price", Int64.Type}, {"Amount", Int64.Type}})
    in
        #"Changed Type"
)
otherwise
    // #table({"Sheet","Customer","Category","Product","Quantity","Price","Amount"},{})
    #table({},{})

In this way, when a sheet cannot be found, it returns an empty table which can be referred to in Table.Combine function.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[mahoneypat · Accepted solution]

If each of your sheets have the same data structure (columns), you should have a single query that combines them all instead of 25 separate queries.

Combine All Sheets in a Workbook with Power Query - Excelerator BI

Pat