Karthi Blog

 STEP-1 Create variables for User Id and Password as shown below. STEP-2 On the connection Manager right click and create a New connection After Creating, right click on the created connection > Properties On Properties click the 3 dots at the right end as shown below. Now select the Properties as shown on the left and assign variables to the properties which you created as shown below in the right Once the connection is created, you will see the fx before the connection as shown below

  You can achieve this by using the   pandas   library in Python. Below is the code to update the   USL   and   LSL   columns to   null   when the   SpecTypeId   is not equal to 5. import pandas as pd import numpy as np # Assuming df is your data frame partspec_insert = df[[ 'PartId' , 'PartRev' , 'TestCodeId' , 'SpecTypeId' , 'Nominal' , 'USL' , 'LSL' , 'TMD' , 'CustomerCoA' ]] # Update USL and LSL to null when SpecTypeId is not equal to 5 partspec_insert.loc[partspec_insert[ 'SpecTypeId' ] != 5 , [ 'USL' , 'LSL' ]] = np.nan # Display the updated data frame print (partspec_insert) Copied Explanation: partspec_insert  is created by selecting the specified columns from the original data frame  df . The  loc  method is used to locate rows where  SpecTypeId  is not equal to 5 and update the  USL  and  LSL  columns to  null  (represented by  np.nan  in panda...

References:  https://learn.microsoft.com/en-us/shows/mvp-azure/pass-parameter-to-sql-queries-statement-using-power-bi https://www.youtube.com/watch?app=desktop&v=3wvnjHPGNQQ&t=0s Power BI Quick Tip: Using Dynamic M Query Parameters with SQL ​​ Step-1 : Create Parameter in Power Bi Create a Parameter in power Query Under Manage Parameter > New Parameter Step-2  : Change values to parameter in where clause of the query Go to "Home" > "Transform Data" to open Power Query Editor. Select the table or query you want to modify. Click on the "Advanced Editor" and modify the query to include the parameters. let // Convert the parameters to text P_YearText = Number.ToText(P_Year), P_SupplierCodeText = Text.From(P_SupplierCode), // Define the SQL query with the parameters Source = Sql.Database("dcazd365dtmigsql01.database.windows.net", "SET", [Query="SELECT * FROM [LSQD].[vwGsqaCoaDataDownload] WHERE [Shi...

 References:  https://www.youtube.com/watch?v=k9DpO91W76o Syntax Example You can create only one column store index, Either Clustered or non-clustered Storage comparison

References : 1)  https://www.youtube.com/watch?v=Iyr7KQUCb0M 2) https://assets.red-gate.com/community/books/inside-the-sql-server-query-optimizer.pdf FILTER DATA  Avoid Using Unnecessary DISTINCT & ORDER BY Create Non-clustered index on frequently used columns in WHERE Clause Avoid applying Functions to columns in Where Clause Avoid Leading Wildcards as they prevent index usage Ensure Columns used in ON clause are Indexed FILTER DATA Filter before Joining Aggregate before joining use UNION instead of OR in JOINS Use SQL HINTS when you are joining small table with big table Use UNION ALL instead of using UNION  when duplicates are acceptable use UNION ALL +DISTINCT when duplicates are not acceptable AGGREGATING DATA Use COLUMNSTORE INDEX for aggregations on large tables. Pre-aggregate the data and store it in new table for reporting SUBQUERIES Don't use IN opeartor instead use JOIN or EXISTS EXISTS is better that JOIN CREATING TABLES Avoid data types VARCHAR & ...

-- Set Identity insert on so that value can be inserted into this column SET IDENTITY_INSERT YourTable ON GO -- Insert the record which you want to update with new value in the identity column INSERT INTO YourTable(UserId, otherCol) VALUES ( 13 , 'myValue' ) GO --Now set the idenetity_insert OFF to back to the previous track SET IDENTITY_INSERT YourTable OFF