Combine and transform issue - Expression.Error: The key didn't match any rows in the table.

[dozer_1985]

Here is the code that it's using to Transform in bold

Transform file (2) step

"= (Parameter2) => let
Source = Excel.Workbook(Parameter2, null, true),
#"Sheet_Suffix" = Source{[Item="SheetSuffix",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet_Suffix", [PromoteAllScalars=true])
in
#"Promoted Headers""

I tried inserting a wildcard instead of the sheet suffix which I thought was stopping this from working... but having looked at other queries. This doesn't seem to be the case. The error I'm getting is at the "Invoke Custom Function" step.

Error: 

An error occurred in the ‘Transform File (2)’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=Sheet_Suffix
Kind=Sheet
Table=[Table]

This is what led me to think it was the filename suffix or sheetname suffix that was the problem. But again, all sheet names match the filename. All follow same naming convention. Filename_Sheet_Suffix. 

I did also try replacing #"Sheet_Suffix" = Source{[Item="SheetSuffix",Kind="Sheet"]}[Data], with #"Sheet_Suffix" = Source{0}[Data],

This fixed the problem with the combine query, but it failed to import some of the data. So wondered if this was a rookie error.Any help appreciated, never used M before. 

[dozer_1985]

Hi John,

Thanks for taking the time to respond. Annoyingly I started trying to implement the advice you gave and found out that it was a corrupt file causing the error, so #"Sheet_Suffix" = Source{0}[Data], works fine now. 

It's good to know that this only works because there's only one tab in each sheet. Not knowing that could have caused issues further down the line. 

I really want to learn some more M at some point. Any pointers for good resources? I usually just Youtube most stuff and do a Udemy course here and there if time permits. 

Really appreciate your time. 

[jbwtp]

Hi @dozer_1985,

try this one: Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo

I would describe it as quite advanced level and you will not need most of the concepts covered in the course, but this will give you exact that - concepts that underlying the M application. I personally refer to it quite often. This is a very good starting point for your M journey.

Another good reading is Chris Webb's BI Blog Chris Webb's BI Blog (crossjoin.co.uk), but this is not a "course", there is a lot of information that you may find not applicable in your day-to-day work, but some of the posts are eye-opening.

Other than that just general search on the Internet. When I was learning M it was context/tasks orientated seach to solve a particular issue. The more you code the easier it becomes. 

Cheers,

John 

[dozer_1985]

Will check them out. Thanks again. 

[jbwtp · Accepted solution]

Hi @dozer_1985,

The reason why it fails with the wildcards is because PQ does not understand the wildcards (not in this case). The Source{0} fails where the required datasheet is not always your first tab (it is internal indexing, nit necessarily reflecting the order of tabs in the book).

There are few ways to resolve it:

1. Just filter the Source to select only items containing Sheet_Suffix (kind of forcing the wildcard): Text.Contains ([Item], "Sheet_Suffix"). Then you should be able (I presume) to use #"Sheet_Suffix" = Source{0}[Data].

2. Alternatively (and in some other cases maybe more reliably) something like: #"Sheet_Suffix" = Source{[Item=Parameter2 & "_Sheet_Suffix",Kind="Sheet"]}[Data] or whatever is needed to dynamically set the sheet name from your filename and the suffix.

Kind regards,

John