SQL Server Innovations

How To Extract data from Sharepoint Online using Power BI Code Snippet as below: let     Source = SharePoint.Files("https://XYZ.sharepoint.com/", [ApiVersion = 15]),     #"Sprint Backlog.xlsx_https://XYZ.sharepoint.com/Reporting  Forms/" = Source{[Name="Sprint Backlog.xlsx",#"Folder Path"="https://XYZ.sharepoint.com/Reporting  Forms/"]}[Content],     #"Imported Excel" = Excel.Workbook(#"Sprint Backlog.xlsx_https://XYZ.sharepoint.com/Reporting  Forms/")      in     #"Imported Excel"

How to Find a Value by searching Multiple Columns in  Power BI Problem: We were combining data from 2 source systems and transforming directly within Power BI and as a result, need to filter Contract Manager which can come from either system Approach: You can create derived column and use CONCATENATE DAX formula to get the value, DAX example as below and then you can use Text Filter - so it can filter in either column DerivedFiledName = CONCATENATE( 'DataSource 1'[DS1FieldName]   ,'DataSource2'[ DS2FieldName])

Organise and Display 3 Monitors I want to connect my laptop to 2 monitors and HP docking station and have struggled on how to display 3 monitors and finally ended up below setting which works nicely for me

Power BI - Import Theme Import the standard theme for your organisation that contains the corporate color palette and a number of standard formats for a number of visuals. Instructions To import your standard theme Open Power BI On the Home ribbon, go to Switch Theme > Import Theme Navigate to the JSON file that stores the theme and select open.  Once the theme is successfully imported all standard colours and a number of formats will be automatically applied to the visuals you use.

How to Connect SQL Server Database Using Microsoft Excel 1. Open Excel 2. From the Data ribbon select the button for From Other Sources and click on From SQL Server 3. From the Data Connection Wizard form enter the following details and click Next> Server name:  Log on credentials: Use Windows Authentication 4. From the next form enter the following details and click Next> Select the database that contains the data you want:  Connect to a specific table: True Highlight the table required 5. From the next form you can keep all the details as they default and click Finish. 6. Excel will then prompt you for how you want to use the imported data

How to Connect SQL Database to Power BI 1. Launch the Power BI Desktop tool and select the Get Data button from the Home ribbon 2. From the Get Data form select source as Sql Server database and click Connect 3. From the SQL Server database form enter the following details and click OK Server:  Database:  Data Connectivity mode: Import 4. You will then be prompted to select the method of connection.  Keep the default of "Use my current credentials" and click Connect. 4. If prompted that the data source is not encrypted then click OK.  You should then see a list of tables available to you to use.

Connect to SQL Database using ODBC connection  Prerequisites Ensure the user has the access privileges.  Check for membership of the right Active Directory Group Ensure the user has required privileges to configure the tool.  (Some may require local Admin rights) Step By Step Process This method is used by the business when typically generating internal analytics through third-party tools (like MS Access) Screenshot 1. Open ODBC.  If using MS Access this can be done via the External Data ribbon. 2. Select Machine Data Source and New 3. Accept warning re non-Admin privileges 4. Select Next (Note, we are creating a Machine DSN) 5. Select SQL Server 6. Accept next alert 7. Enter details as below: Name: must be exact in order to permit other users (with correct DSN setup) to share database queries Description:  (non-critical) Server: (mandatory field) 8. Accept defaults on the next sc...

How to Find SSIS Installed version ? Open  SSIS package check for About Microsoft Visual Studio under HELP menu. There you can find out the Version of SSIS

How to find Oracle Client version Steps Performed: Go to "Control Panel" -> "Administrative Tools" and open "Datasources (ODBC)". By default, the tab "User-DSN" will be opened, click "Add" and a dialogue will pop up:

How to find Microsoft Powershell version Steps Performed: Open Powershell and enter below Command to test version:  $PSVersionTable.PSVersion Snippet as below: