Forum Discussion
Power BI + Transform Step
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
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
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... !
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.