single base from many sources with many sheets - M language

[cosminc]

Hi all

i need to import couple excels from a folder

and each from them has 10-12 sheets - this sheets have unique names and different from an excel to another

 so the result need to be a single base with all these

folder source with:

excel1  -->   sheet1    sheet2   sheet3

excel2  -->   sheet 10  sheet11 sheet12

excel3 -->    sheet20   sheet21  sheet22

excel4 -->    sheet30   sheet31  sheet32

result one single base

is it posible?

Thank,

Cosmin

[Icey · Accepted solution]

Hi @cosminc ,

You can refer to this video. My test result is like below:

The complete code is as follows:

let
   Source = Folder.Files("D:\Power BI\case\excel"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetExcelData", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expanded GetExcelData" = Table.ExpandTableColumn(#"Removed Columns", "GetExcelData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"GetExcelData.Name", "GetExcelData.Data", "GetExcelData.Item", "GetExcelData.Kind", "GetExcelData.Hidden"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded GetExcelData", "NoHeaders", each Table.PromoteHeaders([GetExcelData.Data])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"NoHeaders"}),
    #"Expanded NoHeaders" = Table.ExpandTableColumn(#"Removed Other Columns1", "NoHeaders", {"Segment", "Country", "Product", "Discount Band", "Units Sold", "Manufacturing Price", "Sale Price", "Gross Sales", "Discounts", " Sales", "COGS", "Profit", "Date", "Month Number", "Month Name", "Year"}, {"Segment", "Country", "Product", "Discount Band", "Units Sold", "Manufacturing Price", "Sale Price", "Gross Sales", "Discounts", " Sales", "COGS", "Profit", "Date", "Month Number", "Month Name", "Year"})
in
    #"Expanded NoHeaders"

Best Regards,

Icey Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cosminc]

Hi

it's perfect and very smart

thanks a lot!

Cosmin