Forum Discussion
Power BI + Transform Step
= 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.
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.
- All Power Query functions are case sensitive, and none are ever all caps.
- 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.
- 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.
- 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
5 Replies
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.
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.
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.
- All Power Query functions are case sensitive, and none are ever all caps.
- 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.
- 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.
- 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
