User:Robertcalifornication69/sandbox

Sub App_DeleteQuery Dim M As String, qName As String, qDesc As String Dim qry As WorkbookQuery Dim answer As VbMsgBoxResult Dim LoadToDataModel As Boolean Dim loadToWorksheet As Boolean Dim currentSheet As Worksheet Dim sheet_name As String Application.DisplayAlerts = False Application.ScreenUpdating = False ' We load the query defintions from the Menu worksheet sheet_name = "App_Sub" Sheets(sheet_name).Select qName = Range("D10").Text qDesc = Range("E10").Text M = Range("F10").Text shouldLoadToDataModel = Range("G10") shouldLoadToWorksheet = Range("H10") ' We get from the first worksheets all the data in order to know which query to delete, including its worksheet, connection and Data Model is needed If shouldLoadToDataModel Or shouldLoadToWorksheet Then Dim con As WorkbookConnection Dim conString As String For Each con In ThisWorkbook.Connections If Not con.InModel Then ' This is not a Data Model conenction. We created this connection without the "Power Query - " prefix, to determine if we should delete it, let's check the connection string If Not IsNull(con.OLEDBConnection) Then ' This is a OLEDB Connection. Good chance it is our connection. Let's check the connection string conString = con.OLEDBConnection.Connection Dim prefix As String prefix = "Provider=Microsoft.Mashup.OleDb.1;" If (Left(conString, Len(prefix)) = prefix) And (0 < InStr(conString, "Location=" & qName)) Then ' This is our connection ' It starts with "Provider=Microsoft.Mashup.OleDb.1;" and contains "Location=" with our query name. This is our connection. con.Delete End If               End If            ElseIf (InStr(1, con.Name, "Query - " & qName)) Then ' We created this connection with "Power Query - " prefix, so we can this connection con.Delete End If       Next End If   If shouldLoadToWorksheet Then CleanSheet (qName) End If   If DoesQueryExist(qName) Then ' Deleting the query Set qry = ThisWorkbook.Queries(qName) qry.Delete End If   Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub

Sub DeleteQuery Dim M As String, qName As String, qDesc As String Dim qry As WorkbookQuery Dim answer As VbMsgBoxResult Dim LoadToDataModel As Boolean Dim loadToWorksheet As Boolean Dim currentSheet As Worksheet Dim sheet_name As String Application.DisplayAlerts = False Application.ScreenUpdating = False ' We load the query defintions from the Menu worksheet sheet_name = "Menu" Sheets(sheet_name).Select qName = Range("D10").Text qDesc = Range("E10").Text M = Range("F10").Text shouldLoadToDataModel = Range("G10") shouldLoadToWorksheet = Range("H10") ' We get from the first worksheets all the data in order to know which query to delete, including its worksheet, connection and Data Model is needed If shouldLoadToDataModel Or shouldLoadToWorksheet Then Dim con As WorkbookConnection Dim conString As String For Each con In ThisWorkbook.Connections If Not con.InModel Then ' This is not a Data Model conenction. We created this connection without the "Power Query - " prefix, to determine if we should delete it, let's check the connection string If Not IsNull(con.OLEDBConnection) Then ' This is a OLEDB Connection. Good chance it is our connection. Let's check the connection string conString = con.OLEDBConnection.Connection Dim prefix As String prefix = "Provider=Microsoft.Mashup.OleDb.1;" If (Left(conString, Len(prefix)) = prefix) And (0 < InStr(conString, "Location=" & qName)) Then ' This is our connection ' It starts with "Provider=Microsoft.Mashup.OleDb.1;" and contains "Location=" with our query name. This is our connection. con.Delete End If               End If            ElseIf (InStr(1, con.Name, "Query - " & qName)) Then ' We created this connection with "Power Query - " prefix, so we can this connection con.Delete End If       Next End If   If shouldLoadToWorksheet Then CleanSheet (qName) End If   If DoesQueryExist(qName) Then ' Deleting the query Set qry = ThisWorkbook.Queries(qName) qry.Delete End If   Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub

Sub CleanSheet(ByVal sheetName As String) ' Helper function to try to delete the worksheet if exists On Error Resume Next ThisWorkbook.Sheets(sheetName).Delete End Sub

Function DoesQueryExist(ByVal queryName As String) As Boolean ' Helper function to check if a query with the given name already exists Dim qry As WorkbookQuery If (ThisWorkbook.Queries.Count = 0) Then DoesQueryExist = False Exit Function End If   For Each qry In ThisWorkbook.Queries If (qry.Name = queryName) Then DoesQueryExist = True Exit Function End If   Next DoesQueryExist = False End Function

Sub App_Sub

Dim M, qName, qDesc As String Dim qry As WorkbookQuery Dim currentSheet As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Call setup_ ' We load the query defintions from the Menu worksheet sheet_name = "App_Sub" Sheets(sheet_name).Select qName = Range("D10").Text qDesc = Range("E10").Text M = Range("F10").Text If DoesQueryExist(qName) Then ' This query already exists We will delete it first App_DeleteQuery ' In case we have worksheet that was created by this macro for the new query, let's delete it       CleanSheet (qName) End If   ' The new interface to create a new Power Query query. It gets as an input the query name, M formula and description Set qry = ThisWorkbook.Queries.Add(qName, M, qDesc) shouldLoadToDataModel = Range("G10") shouldLoadToWorksheet = Range("H10") ' We get from the first worksheets all the data in order to know which query to delete, including its worksheet, connection and Data Model is needed If shouldLoadToWorksheet Then ' We add a new worksheet with the same name as the Power Query query Set currentSheet = Sheets.Add(After:=ActiveSheet) currentSheet.Name = qName If Not shouldLoadToDataModel Then ' Let's load to worksheet only LoadToWorksheetOnly qry, currentSheet Else ' Let's load to worksheet and Data Model LoadToWorksheetAndModel qry, currentSheet End If   ElseIf shouldLoadToDataModel Then ' No need to load to worksheet, only Data Model LoadToDataModel qry End If   Sheets(sheet_name).Select Application.ScreenUpdating = True Application.DisplayAlerts = True

End Sub

Sub CreateQuery

Dim M, qName, qDesc As String Dim qry As WorkbookQuery Dim currentSheet As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Call setup_ ' We load the query defintions from the Menu worksheet sheet_name = "Menu" Sheets(sheet_name).Select qName = Range("D10").Text qDesc = Range("E10").Text M = Range("F10").Text If DoesQueryExist(qName) Then ' This query already exists We will delete it first DeleteQuery ' In case we have worksheet that was created by this macro for the new query, let's delete it       CleanSheet (qName) End If   ' The new interface to create a new Power Query query. It gets as an input the query name, M formula and description Set qry = ThisWorkbook.Queries.Add(qName, M, qDesc) shouldLoadToDataModel = Range("G10") shouldLoadToWorksheet = Range("H10") ' We get from the first worksheets all the data in order to know which query to delete, including its worksheet, connection and Data Model is needed If shouldLoadToWorksheet Then ' We add a new worksheet with the same name as the Power Query query Set currentSheet = Sheets.Add(After:=ActiveSheet) currentSheet.Name = qName If Not shouldLoadToDataModel Then ' Let's load to worksheet only LoadToWorksheetOnly qry, currentSheet Else ' Let's load to worksheet and Data Model LoadToWorksheetAndModel qry, currentSheet End If   ElseIf shouldLoadToDataModel Then ' No need to load to worksheet, only Data Model LoadToDataModel qry End If   Sheets(sheet_name).Select Application.ScreenUpdating = True Application.DisplayAlerts = True 'ThisWorkbook.RefreshAll End Sub

Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet) ' The usual VBA code to create ListObject with a Query Table ' The interface is not new, but looks how simple is the conneciton string of Power Query: ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name With currentSheet.ListObjects.Add(SourceType:=0, Source:= _       "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _       , Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdDefault .CommandText = Array("SELECT * FROM [" & query.Name & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = False .Refresh BackgroundQuery:=False End With End Sub

Sub LoadToWorksheetAndModel(query As WorkbookQuery, currentSheet As Worksheet) ' Let's load the query to the Data Model LoadToDataModel query ' Now we can load the data to the worksheet With currentSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _       Connections("Query - " & query.Name), Destination:=Range("$A$1")).TableObject .RowNumbers = False .PreserveFormatting = True .PreserveColumnInfo = False .AdjustColumnWidth = True .RefreshStyle = 1 .ListObject.DisplayName = Replace(query.Name, " ", "_") & "_ListObject" .Refresh End With End Sub

Sub LoadToDataModel(query As WorkbookQuery) ' This code loads the query to the Data Model ThisWorkbook.Connections.Add2 "Query - " & query.Name, _ "Connection to the '" & query.Name & "' query in the workbook.", _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _ , """" & query.Name & """", 6, True, False

End Sub

let Source = Sql.Database("EDWDB-APS.PROD.CAN,17001", "EDW_PROD", [Query="SELECT DISTINCT in   Source
 * 1) (lf)t2.ACCOUNT_KEY,
 * 2) (lf)t2.ACCT_NUM,
 * 3) (lf)t2.ACCT_STATUS_KEY,
 * 4) (lf)t4.CREDIT_SCORE,
 * 5) (lf)t5.ACCEPTED_CREDIT_LIMIT_AMT,
 * 6) (lf)t1.ACCT_OPEN_DT,
 * 7) (lf)t1.ACCT_CLOSE_DT
 * 8) (lf)FROM
 * 9) (lf)EDW_VIEWS.F_CRDT_CARD_KEY_MEAS_DAILY as t1,
 * 10) (lf)EDW_VIEWS.F_ACCT_PRFL as t2
 * 11) (lf)LEFT JOIN EDW_VIEWS.D_CRDT_CARD_APPLICATION as t3 ON t2.ACCT_NUM = t3.ACCOUNT_NUM
 * 12) (lf)LEFT JOIN EDW_VIEWS.F_CRDT_CARD_APPLICANT_PRFL as t4 ON t3.CRDT_CARD_APPLICATION_KEY = t4.CRDT_CARD_APPLICATION_KEY
 * 13) (lf)LEFT JOIN EDW_VIEWS.F_CRDT_CARD_APPLICATION_SNP as t5 ON t4.CRDT_CARD_APPLICATION_KEY = t5.CRDT_CARD_APPLICATION_KEY
 * 14) (lf)WHERE
 * 15) (lf)t1.ACCOUNT_KEY = t2.ACCOUNT_KEY
 * 16) (lf)AND t1.CUSTOMER_KEY = t2.CUSTOMER_KEY
 * 17) (lf)AND t1.ACCT_CLOSE_DT IS NULL
 * 18) (lf)AND t1.ACCT_OPEN_DK <> t1.ACCT_CLOSE_DK
 * 19) (lf)AND t1.SNAPSHOT_DT >= '10/1/2019'
 * 20) (lf)AND t1.ACCT_OPEN_DT >= '10/1/2019'
 * 21) (lf)"])