Power BI + Transform Step

[stvn43]

= Table.AddColumn(#"Removed Columns", "Operation", each IF(ISNUMBER(SEARCH("DropOff",[file_fullpath])), "Upload","Transfer"))

I keep getting this error: Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly

and can't see why -- any help appreciated.

[edhans]

Power Query syntax is TOTALLY different than DAX. Your formula will not work. 

Can you provide some data to work with, and explain what full path is? SEARCH() works with text and will never return a number, for example, so even using DAX functions, your request isn't clear.

[stvn43]

I've been using MySQL Workbench and Excel to produce these scorecards and thought I would switch to Power BI to automate the process somewhat. My original formula was created in Excel:

= IF(ISNUMBER(SEARCH("DropOff",I2)), "Upload","Transfer") -- basically looks for the text values in the "full_path" string and updates the column/row. The column name is [full_path] in my posted example.

I pulled the data from MySQL using Power BI and moved to the Transform step and tried to insert a Custom Column and have it use the formula.

[edhans · Accepted solution]

Ok, well, you cannot use Excel formulas in Power Query, and without some data I cannot really provide a formula that will work as I am guessing. However, a few tips.

1. All Power Query functions are case sensitive, and none are ever all caps.
2. IF() in Power Query is if this then that else something. So no commas, and if/then/else is lowercase. So if [Column1] = "Test" then 1 else 0, and the else is never optional. You must always provide an else condition.
3. ISNUMBER might be replaced by Value.Is() - and it is camelcase as I spelled it there. But it isn't as easy to use as ISNUMBER but without data, hard to help.
4. SEARCH would probably be replaced with Text.Contains() which you can read about here. It is a case sensitive search unless you use the Comparer.OrdinalIgnoreCase parameter in the 3rd place. so Text.Contains([Your Text Field], "Search Text", Comparer.OrdinalIgnoreCase) would return true or false if "Search Text" was found in [Your Text Field] regardless of the case.

If you need more help, please provide data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[stvn43]

Yes, I'm new to Power Query (invoked once I use the "transform data" feature from the PBI Desktop), but committed to using it more. The data is basically from an Aspera log. Here's a sample. My old Excel formula parsed the "file_fullpath" column looking for the "DropOff" text. All the data fed to a pivot that generates a scorecard. 

created_at	started_at	stopped_at	file_fullpath	file_index	file_basename	source_item	size
########	########	########	/bu-vault1/PROD/AsperaLP/MCCANN_NEW_YORK/NEW_YORK_LOTTERY/DropOff/YNSL3388000H.mov	1.6E+09	YNSL3388000H.mov	NULL	1.33E+09
########	########	########	bu-vault1/PROD/AsperaLP/MCCANN_NEW_YORK/NEW_YORK_LOTTERY/DropOff/YNSL3386000H.mov	1.6E+09	YNSL3386000H.mov	NULL	6.9E+08
########	########	########	/bu-vault1/PROD/AsperaLP/MCCANN_NEW_YORK/NEW_YORK_LOTTERY/DropOff/YNSL3384000H.mov	1.6E+09	YNSL3384000H.mov	NULL	6.76E+08

Thanks for you comments, very helpful. Reading up as fast as I can on the M language structure... !

[edhans]

I think all you need is this:

if Text.Contains([file_fullpath], "dropoff", Comparer.OrdinalIgnoreCase) then "Upload" else "Transfer"

You do not need ISNUMBER/Value.is() here as Text.Contains returns a simple true false. I'd have to go back to Excel but I think SEARCH there returns an error if not found or a number of the position it finds the text.