Forum Discussion
Data modeling and table lengths in Excel
Hi,
In an Excel solution, I bring in data from SQL connection to worksheet 1 (WS#1), thus the data set number of rows changes each time. A second worksheet (WS#2)runs calculation on WS#1. My problem starts when the WS#2 ends up with bunch of N/A or Errors dow at the button due to the table size not matching to the correspond number of rows pulled from WS#1. I suspect this must be a clear question for pro data modelers? I appreciate any help.
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)
5 Replies
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)
