Forum Discussion

skhalatian's avatar
skhalatian
Frequent Visitor
8 years ago

M query extract Excel worksheet name

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

Show and Tell
Tips and Tricks

1 Reply

  • v-fengyz-msft's avatar
    v-fengyz-msft
    Helper I
    8 years ago

    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,