M query extract Excel worksheet name

[skhalatian]

Hi,

- I have a function for merging few XL speadsheets

- each spreadsheet has got one worksheet ( the worksheet name for all speedsheets are the same (WS))

the function works as expected, the issue is that if I have a spreadsheet with worksheet  named different than WS then it will break

I want to be able to load XL file that have one worksheet and the function merge them regardless of the name of the worksheet

currenly I'm using the folloing code

let
    Source = (#"Sample File Parameter1") => let
        Source = Excel.Workbook(#"Sample File Parameter1", null, true),
        WS_Sheet = Source{[Item="WS",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(WS_Sheet , [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    Source

Thanks

Shaun

[v-fengyz-msft]

@shaun,

If one spreadsheet only has one worksheet or the worksheet you required always occurred in fixed location of multiple worksheets (e.g. always the first worksheet ), then you can exact the data

of worksheet using below sample code:

let

    Source = (#"Sample File Parameter1") => let

        Source = Excel.Workbook(File.Contents(#"Sample File Parameter1"), true),

       //this line return data of first worksheet and ignore the name of worksheet

       WS_Sheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],

        #"Promoted Headers" = Table.PromoteHeaders(WS_Sheet, [PromoteAllScalars=true])

    in

        #"Promoted Headers"

in

Source

If not, please share some logics to meet your requirement.

Regards,