REF Error when referencing power query table

Question

guys,

Making great progress learning power query via StackOverflow! Let me describe my problem and the steps I've taken thus far:

So far I've followed guides on how to combine multiple files via a function and its actually worked great so far. I get data from a folder where all my files are, they are combined, "cleaned" to show only the columns I want, and then loaded into excel as a table... great!

Name         | fCleanLogger.Temp (F)
------------ | --------
Logger A.txt | 78
Logger A.txt | 79
Logger A.txt | 57
Logger B.txt | 66
Logger B.txt | 90
Logger B.txt | 48
Logger B.txt | 44

The trouble starts when I want to reference that table for use in my "summary" sheet.

Example: I have 10 files. I want to get the max temperature value of each and every one of those files... but all those files are now combined. So I have to do an INDEX MATCH formula...

=INDEX(MAX(Excel_LogFiles[fCleanLogger.Temp (F)]), MATCH("Logger A.txt", Excel_LogFiles[Name], 0))

=INDEX(MAX(Excel_LogFiles[fCleanLogger.Temp (F)]), MATCH("Logger B.txt", Excel_LogFiles[Name], 0))

The strange thing is, it works perfectly when I use the formula for matching "Logger A.txt.". But then every other file name gives a #REF error.

Anybody have any suggestions? Very stumped on this one and don't know where to look for help.

Thanks so much in advance!

teylyn teylyn · Accepted Answer · 2017-09-26T02:54:44

Index takes a range as the first argument. You are feeding it a Max statement instead. The first formula only returns a result because the Match returns a 1 and the first argument of the Index has one number as the result. The second formula fails because the Max still only returns one number (i.e. 90), but the Match now returns a 4 and there is only one value in the index range, i.e. 90).

If you have an Office 365 subscription, you can use the Maxifs function

=MAXIFS(Excel_LogFiles[fCleanLogger.Temp (F)],Excel_LogFiles[Name],E3)

If you run another version, you can use this array formula, which must be confirmed with Ctrl-Shift-Enter

=MAX(IF(Excel_LogFiles[Name]=E6,Excel_LogFiles[fCleanLogger.Temp (F)]))

Power Query has nothing to do with this, by the way. It's just Excel.

REF Error when referencing power query table

1 Answers