I have completed the PowerQuery course and haven't seen a way to look up, say, the status of a company (appearing multiple times in my query) in a local dimension table and add a column to the query containing that status or, if the company is not found in the dimension table, put a default value.
In other words "if this company is in my dimension table with a status of Gold or Silver, add that status to the query records, otherwise add "No Status".
Is there an M equivalent to Excel's
IF(ISERROR(VLOOKUP(company, Dimension table,2,false)),"No Status",Vlookup(company, Dimension table,2,false)) ?
I apologise in advance if this has already been answered somewhere and I failed to notice it 🙂
Thanks,
Peter
Hi Peter,
The equivalent of a VLOOKUP is to merge tables. Once you've merged the tables you can expand the matching column. If there's a match, that column will contain the matching value and if there isn't a match, it will be empty (null). You can then add a column that performs an action based on whether that expanded column contains null or not.
See lesson 3.04 of the Power Query course for Merge.
Mynda
Two follow-on questions:
1) Could you explain the pros and cons (or elegance) of the merge method versus just connecting in the data model the company status dimension table to the query via a Many (in query) to 1 (in status dimension table) connection on the company name and then just bringing the status result field into a pivot table reporting on the query? I can think of one, if a company is not in the status table, it will return a blank in the pivot table for company status.
2) when I use the merge method, the dialog tells me there are no matches between the company names in the query and the company names in the dimension table, until I turn on fuzzy matching, where it tells me a reasonably correct count of matches in both tables. Any idea what would be the cause of this ( trailing spaces have been ruled out, would non-unicode/unicode cause this)?
Hi Peter,
1. The status dimension table can not be a dimension table because every company is not listed. i.e. it only lists those that have a status. If you want to bring in other data for the companies that maybe have values, but no status, only those with a status would be included in the report. In your example, if a company is not in the status table it would be completely omitted from the PivotTable. To be clear, it wouldn't even return a blank.
2. Power Query is case sensitive. If the names in one table have upper and lower case and in the other table they're only lower case, then it won't find a match. Fuzzy matching might get around this, but I wouldn't rely on it. Of course non-printing characters can also cause mismatches.
Mynda
Hi Mynda,
Thanks for your prompt responses!
1) I have successfully used the merge function in PowerQuery (see 2 below) and then replaced the null values with "No Status"
because
2) There were trailing blanks in the data coming from the query which I had failed to detect. It looks as though they auto-trimmed if you loaded to a table (and I had performed a Len(text) function to check) but kept trailing spaces if loaded only to the data model. It seems that all company names were padded to a 60 char width in the originating database.
Anyway I applied the Trim function in PowerQuery and now merge works as expected.
Thanks for your excellent course material!
Peter
So pleased to hear you got it working, Peter!
