How to get all sheets as a table in power bi

[komald]

I have a folder which consist 3 excel file and each file has 3 sheet I want to fetch all these sheets as a Table i.e 9 tables in power bi( I dont want to combine files as it consist independant data) . How to do that?

Data source will be folder path

[AntrikshSharma · Accepted solution]

@komald Use this:

let
    Source = Folder.Contents ( "C:\Users\SharmaAnt\Downloads\Test" )[[Content]],
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Custom",
            each Table.SelectRows ( Excel.Workbook ( [Content] ), each [Kind] = "Table" )[Data]
        ),
    ExpandedCustom = Table.ExpandListColumn ( AddedCustom, "Custom" )
in
    ExpandedCustom