Author Topic: Power Query ForEach error  (Read 209 times)

stanl

  • Pundit
  • *****
  • Posts: 791
Power Query ForEach error
« on: June 04, 2018, 05:25:51 am »
Looking into using WB to automate some Power Query (Excel 2016) activity. Ultimately would like to be able to create a data model from scratch, including entering M code and automating DAX.  For starters I have a data model with 4 queries  [based on the Microsoft FoodMart .csv files].  After loading the datamodel .xlsx file I run a simple ForEach loop:

Code: Winbatch
ForEach cn In oXL.Activeworkbook.Connections
   If StrIndex(cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1",0,@FWDSCAN) >0
           Display(2,"Power Query Connection",cn.OLEDBConnection.Connection)
           cn.Refresh()
   Endif
Next
 

which works perfectly for the 4 queries then errors before exiting the loop. (see attached).  I do not want to use refreshall() as there are known timeout issues. Also don't want to turn errormode off.  What I did notice is when adding

Code: Winbatch
Message("Connections",oXL.Activeworkbook.Connections.Count)
 

it returns 5, as there is a connection called DataModel which has no description or properties associated with it. Not sure if it would always be the last one so a for n=1 to count-1 could fail especially if other connections not associated with the data model were present. So I guess I am asking for suggestions on how to trap for this in the original ForEach loop.


[EDIT]

Fixed by adding another if....
Code: Winbatch
ForEach cn In oXL.Activeworkbook.Connections
   If StrIndex(cn.name, "Query - ",0,@FWDSCAN) >0
      If StrIndex(cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1",0,@FWDSCAN) >0
              Display(2,"Power Query Connection",cn.OLEDBConnection.Connection)
              cn.Refresh()
      Endif
   Endif
Next