Load from folder with multiple sheets

[KyawMyoTun]

Dear Experts,

I am having a problem with load from folder with multiple sheets.
1) I would like to get the dates in row which are defines in columns(for June 2021).
    I would like to get these also for the upcoming dates(eg from July till end) which are plcaed month by month under a folder. that's why I want to load from folder.

2) I would like to load the different sheets and like to appen to get the consolidated file.
    But these sheets having different number of columns as below.

So, could you please look into this and I am hoping to find the solution.
Thank you very much on your help.

Best Regards,
Kyaw Myo Tun

[v-yingjl · Accepted solution]

Hi @KyawMyoTun ,

To import multiple sheets in power query, you can try to create a custom function like this:

(PageNumber as text) =>

let
    Source = Folder.Files("C:\Users\xxx\xxxx\xxxxx"),
    #"C:\Users\xxx\xxx\xxxx\_test2 xlsx" = Source{[#"Folder Path"="C:\Users\xxx\xxxx\xxx x\",Name="test2.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"C:\Users\xxx\xxx\xxx\_test2 xlsx"),
    #"1_Sheet" = #"Imported Excel"{[Item=""&PageNumber&"",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"1_Sheet", [PromoteAllScalars=true])
in
    #"Promoted Headers"

You can enter the sheet number to import specific sheets into power query by this function.

For appending tables with different columns, not need to worry, the appended table would show all the columns from all appended tables. If one table does not have some columns, it will show null value under these columns.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.