Jul 12 2019 10:13 AM
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.
Jul 14 2019 02:26 PM
@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.
Nov 27 2019 04:25 PM
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?
Nov 27 2019 05:54 PM - edited Nov 27 2019 06:02 PM
Nov 27 2019 05:54 PM - edited Nov 27 2019 06:02 PM
SolutionThere 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)
Nov 28 2019 09:26 AM
Nov 28 2019 12:07 PM
You're welcome! Glad it worked as desired.
Nov 27 2019 05:54 PM - edited Nov 27 2019 06:02 PM
Nov 27 2019 05:54 PM - edited Nov 27 2019 06:02 PM
SolutionThere 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)