Unable to Open .xls Files From Azure Data lake

[vega]

Hello,

I am trying to open some .xls files that are located in Azure Data Lake. When I use Get Data -> More -> Azure -> Azure Data Lake, navigate to where the files are, and try to combine the files I get the following error:


However, if download the .xls files to my local machine and use Get Data -> More -> Folder, point it to the local copies and combine the files, the process works as expected and I am able to see the contents of the files. Does anyone have any suggestions as to why this is the case or what can be done to resolve it?

Thanks

[v-shex-msft]

Hi @vega,

You can refer to below steps to combine these xls files.

1. get data from azure data lake.

2. Navigate to files list.

3. Add custom column to analysis excel files.

#"Added Custom" = Table.AddColumn(Test, "Custom", each Table.PromoteHeaders(Excel.Workbook([Content])[Data]{0})),

4. Use Table.Combine function to merge these files.

Full query:

let
    Source = DataLake.Contents("xxxxxxxxxxxxxx"),
    Test = Source{[Name="Test"]}[Content],
    #"Added Custom" = Table.AddColumn(Test, "Custom", each Table.PromoteHeaders(Excel.Workbook([Content])[Data]{0})),
    Custom1 = Table.Combine(#"Added Custom"[Custom])
in
    Custom1

Regards,

Xiaoxin Sheng