Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi
it's perfect and very smart
thanks a lot!
Cosmin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |