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
