Forum Discussion
Data modeling and table lengths in Excel
There is Text.PositionOf function in Power Query which returns the position of the sub-string in a Text Column and if the sub-string is not found, it returns -1.
The syntax would be like this...
=Text.PositionOf(<Text Column>, "<sub-string being searched>")
You may replace the -1 with 0 in the added column if the sub-string was not found like this...
=Replacer.ReplaceValue(Text.PositionOf(<Text Column>, "<sub-string being searched>"),-1,0)
Nima Mohandesan What formulas are you using to arrive at the numbers? Maybe you can perform these calculations in Power Query instead of worksheet formulas, so they get added to all rows of the query.
If that is not an option and you want to pre-fill your calculation formulas without getting errors, you can wrap them in an IF statement that checks how many rows of data exist in the query. For example, if the query results start in column A in row 1 of the sheet called "Data", then you can do this on the calculation sheet
=if(counta(Data!$A:$A)>=row(), YourCalculation,"")
So, if the count of populated rows in column A is bigger than the row number of the current row, perform your calculation, otherwise return a blank.
Let me know if that will work for you.
Hi,
Adding formulas to Power Query would work. I have many formulas to add, for instance FIND. I'd like to add a new column that returns position of a phrase in a string. I used Add Column/Custom Column but it seems like it doesn't like FIND as one of functions. Am I on a wrong path here?
There is Text.PositionOf function in Power Query which returns the position of the sub-string in a Text Column and if the sub-string is not found, it returns -1.
The syntax would be like this...
=Text.PositionOf(<Text Column>, "<sub-string being searched>")
You may replace the -1 with 0 in the added column if the sub-string was not found like this...
=Replacer.ReplaceValue(Text.PositionOf(<Text Column>, "<sub-string being searched>"),-1,0)
You're welcome! Glad it worked as desired.