Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sabedin
Helper I
Helper I

Loading data from folder, different sheet names

Hello,

 

I have Excel files with data in same format, but the sheet names in the files are different. This brings an error "the key didn't match any rows in the table” when loading the files. 

 

This link

 

https://social.technet.microsoft.com/Forums/windowsserver/en-US/b9e50702-ff16-4639-9d6b-8b3b4635847e...

 

shows that there is a fix about this, but I don't know how to implement the solution. 

 

This is the code in the advanced query editor:

 

let
Source = Folder.Files("D:\PBI_pumilamac"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from PBI_pumilamac", each #"Transform File from PBI_pumilamac"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from PBI_pumilamac"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from PBI_pumilamac", Table.ColumnNames(#"Transform File from PBI_pumilamac"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Export20171022.xlsxExport20171022.xlsxExport20171022.xlsx", type text}, {"ExportID", type text}, {"Product", type text}, {"Vendor_Name", type text}, {"Brand", type text}, {"ProdType", type text}, {"CL", type text}, {"CloseDate", type any}, {"Name", type text}, {"QtyAvl", Int64.Type}, {"QtyOrd", Int64.Type}, {"QtyOnHand", Int64.Type}, {"AvgSalesWkly_26", type number}, {"Total_ExtPDL_Cost", type number}, {"ProdShBoxHeight", type any}, {"ProdShBoxWidth", type any}, {"ProdShBoxLength", type any}, {"ProdShBoxCubic", Int64.Type}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type1", "Text Between Delimiters", each Text.BetweenDelimiters([Export20171022.xlsxExport20171022.xlsxExport20171022.xlsx], "t", ".", 0, 0), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", "Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Export20171022.xlsxExport20171022.xlsxExport20171022.xlsx"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([CloseDate] <> "Close" and [CloseDate] <> "Date"))
in
#"Filtered Rows"

 

Thanks

Sabedin

1 ACCEPTED SOLUTION

here is link to pbix file , it has two functions, read sheets from excel files and then read data from each sheet in excel file.

 

feel free to reach out if you need further help./

https://drive.google.com/file/d/0B4dPgH9_BPBraFhtTVdnd05OSnM/view?usp=sharing


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

It is doable, does the files have more than one sheet or just always one sheet and sheet names are different?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Only one sheet

 

Thanks

ok i have a solution just created, will share with you, it will read all the excel files from a folder and all the sheets frm a file (even if there are more than one). you can change stuff as you want.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

here is link to pbix file , it has two functions, read sheets from excel files and then read data from each sheet in excel file.

 

feel free to reach out if you need further help./

https://drive.google.com/file/d/0B4dPgH9_BPBraFhtTVdnd05OSnM/view?usp=sharing


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks for this, I tried but no luck... I tried to match parameters, but it seems that this is too complicated for me.

 

Did you check the link I posted? It seems to be an easier solution to this?!

 

Thanks

Sabedin

You don't need to do anything with parameters.


That is for internal stuff. You just need to give path where your files are and rest will work.


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hello,

 

I had to delete the last step, because it was requiring column from your files, and it seems to work now 🙂

 

Thanks a lot again Sir.

Sabedin

@sabedin

 

Glad to hear, sorry I missed to mention that but you got it. cheers



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi! 

I'm still a novice at Power BI, but I'm trying to figure out how to properly use your functions. Am I supposed to copy and paste the M language in the Advanced Editor for each function into my own PBIX file where my data is at? And then from there, once it's been set up, what are my next steps?

Similar to the person who posted this question, I have several excel files where the format is the same (Profit Loss statements), but each tab represents a different entity for the P-L. I'm trying my best to transform them, but my prior attempts don't go anywhere. I'm curious to see if your solution works, but I don't know how to use your functions. I'd so appreciate a walk-through step-by-step, especially since I've been using Power Bi for only two weeks 🙂

Thanks!

Anonymous
Not applicable


@Anonymous wrote:

Hi! 

I'm still a novice at Power BI, but I'm trying to figure out how to properly use your functions. Am I supposed to copy and paste the M language in the Advanced Editor for each function into my own PBIX file where my data is at? And then from there, once it's been set up, what are my next steps?

Similar to the person who posted this question, I have several excel files where the format is the same (Profit Loss statements), but each tab represents a different entity for the P-L. I'm trying my best to transform them, but my prior attempts don't go anywhere. I'm curious to see if your solution works, but I don't know how to use your functions. I'd so appreciate a walk-through step-by-step, especially since I've been using Power Bi for only two weeks 🙂

Thanks!



Hi - Did you ever find the solution for this? 

 

Many thanks

Anonymous
Not applicable

Hi,

 

This post together with my excperience confirm that Microsoft Power BI cannot import several excel files with different sheet names through import folder. Is it true?

 

The solution of this post may work, but it is not generalized for all similar cases. I look forward to a genearl solution for this problem. Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.