Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Solved! Go to 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.
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
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
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.
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 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
I don't know if this will help your use case, but it helped me -
https://www.howtoexcel.org/power-query/how-to-import-multiple-files-with-multiple-sheets-in-power-qu...
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
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |