How to switch between reports using Bookmarks in Power BI

Before creating any bookmarks in power Bi, please find the below instructions.

Make sure you are enabled the Bookmarks pane in your desktop version. To enable the bookmarks pane please go to view option on your power bi desktop and tick on the Bookmarks pane.

For reference see the below example.

I have two reports called Visual and Table. Both reports contains Total revenue by city.

To create a bookmark in Visual page do the followings steps.

1. Go to Home - Find Button menu.
2. On Button menu select Bookmark Option from drop down list.
3. Go to Table report.
4. Go to bookmark pane on right navigation bar and add a new bookmark and rename it.
5. Go to Visual page and click on the Bookmark button you are selected.
6. Once you will click on the Bookmark button go to Visualizations pane on right side.
7. Go to Action in Visualizations pane.
8. Select Bookmarks in Type option.
9. Choose the bookmark name that you have created and select it on Bookmark option.
10. Click on the Bookmark button CTRL+Click.

CTRL + Click on bookmark button it easily toggle to the table report.

How can Use Tooltip in Power BI

You can create visually rich report tooltips that appear when you hover over visuals, based on report pages you create in Power BI Desktop. The Tooltips will appear from one page to another page once you are putting your mouse over the visual.

To create a tooltip page, please do the following steps.

1. Go to Power BI Desktop
2. Go to page properties, page size and set your layout accordingly.
3. Take any visual, can use Donut Chart
4. Insert the field value.

To create a main page or called Home page.

1. Take any visual like Ribbon chart
2. Put your fields as per report creation
3. Click on the Ribbon Chart
4. Go to properties
5. Put ON tooltip option.
6. Set Type like Report page
7. Then add Page as your tooltip page Name.

Power BI useful Formulas

Power BI Aggregate functions


Measure Name = SUM(Expression or Column Name)

Measure Name = AVERAGE(Expression or Column Name)

Measure Name = MIN(Expression or Column Name)

Measure Name = MAX(Expression or Column Name)

MaxSale = MAX(Expression or Column Name)

Measure Name = COUNT(Expression or Column Name)

Measure Name = VAR.S(Expression or Column Name)

Measure Name = VAR.P(Expression or Column Name)

Measure Name = STDEV.P(Expression or Column Name)

Measure Name = STDEV.S(Expression or Column Name)

Measure Name = MAX(Expression1, Expression2)                             --2 args

Measure Name = MIN(Expression1, Expression2)                             --2 args




Create a new table from existing table

1.       Table_Name = related(Existing_TableName)

2.       NewTableName = SUMMARIZE(ExistingTableName, ExistingTableName[column1], ExistingTableName[column2])                   --selected columns



Power BI DATE functions

1.       Day= DAY(NewTableName[OrderDate])                                                 – day from date column

2.       MonthinDate = MONTH(EmployeeSales[HireDate])

3.       YearInDt = YEAR(EmployeeSales[HireDate])

4.       Hour = HOUR(EmployeeSales[HireDate])

5.       Min = MINUTE(EmployeeSales[HireDate])

6.       Sec = SECOND(EmployeeSales[HireDate])

7.       Date1 = DATE(YEAR(NewTableName[Date]), MONTH(NewTableName[Date]),                 

8.       DAY(NewTableName[Date]))

9.       WeekDay = WEEKDAY(NewTableName[OrderDate], 1)    --If Number = 1 Week begins on Sunday, and If Number = 2 then Week begins on Monday. Let me create a column to find the week number

10.   WeekNum = WEEKNUM(EmployeeSales[HireDate], 1)

11.   YearFrac = YEARFRAC(EmployeeSales[HireDate], NOW())

12.   DateInMonth = FORMAT(NewTableName[OrderDate],"mmmm")

Let me add 2 months to Hire Date on Existing month

13.   Edate = EDATE(EmployeeSales[HireDate], 2)        --adding 2 months

14.   Eomonth = EOMONTH(EmployeeSales[HireDate], 3)         --adding 3 months

15.   DATEDIFF(Start_Date, End_Date, Difference_Value)         ---dateDiff

16.   DateDiff = DATEDIFF(EmployeeSales[HireDate], NOW(), YEAR)    –dateDiff in year

17.   Calender = CALENDAR(DATE(2018,1, 1), DATE(2018, 1, 15))           –creating a date table from a given range

18.   CalenderAutotable = CALENDARAUTO()                 –will create a new date table and will take auto date from existing table



Power BI Logical functions

1.       IfElse = IF(NewTableName[WeekDay]=23,"Yes","No")

2.       Column = IF(NewTableName[IFLeng]=2,FORMAT(NewTableName[IFLeng],"00"))                 -- if IFLeng=2, then add one preceding zero.

3.       Column 2 = IF(NewTableName[WeekDay]>20,"Greater","Smaller")



4.       NestedIfEx = IF(NewTableName [WeekDay] < 30, "Lesser than 30",       IF(NewTableName [WeekDay] > 30, "Good", "Greater than 30" ))



5.       IFAND = IF(AND(NewTableName[WeekDay]>20,NewTableName[WeekDay]<30),"Between Thirty","Not in 20-30")



6.        IFOR = IF(OR(NewTableName[WeekDay]=30,NewTableName[WeekDay]=20),"Thirty-Twenty")



7.       Number_Of_Rows = COUNTROWS('Sales Data') – Your table_name.

8.       IfNot = NOT(IF(NewTableName[WeekDay]>30,"True","False"))



9.       SwitchMonth = SWITCH(MONTH(NewTableName[OrderDate]), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May",12, "December", "Unknown")  --If jan,feb or other months are not matching with their respective serial number , will return Unknown else same month name.

Max cost in Power BI

To find the maximum cost in Power BI please do the all following steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

MaxOf = MAX(Table1[Cost])

Min Cost in Power BI

To find the minimum cost in Power BI please do the all following steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

MinOf = min(Table1[Cost])  

Sum in Power BI

To do sum in Power BI please do the all following steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

Sum_Example = SUM(Table1[Cost]) 

Nested If in Power BI

To create a nested if please follow the below steps

1. Open your Power BI desktop 
2. Go to Modelling 
3. Go to New Column
4. Add this below formula.

NestedIfEx = IF(NewTableName [WeekDay] < 30, "Lesser than 30",

IF(NewTableName [WeekDay] > 30, "Greater than 30"))

empty box

If Else condition in Power BI

If Else condition in Power BI: -



   Open Your data set in Power BI desktop



2. Go to Query
Editor





3 . Go to Add
Column





4.Go to
Custom Column





5. Change your
header name

Output:

Total Number of Rows from a table in Power BI

Total Number of Rows in a Table: -



Go to Desktop mode on Power Bi
Go to Modelling
Go to New Column
Add this Formula
 Number_Of_Rows = COUNTROWS('Sales Data') – Your table_name.
   Enter


It will create a new column and will update the total number of rows from your table.

Serial number in all sheets excel macros

Sub Serial_number()
On Error Resume Next

Dim tot_Sheets As Integer
tot_Sheets = Application.Sheets.Count
Dim s As Integer
For s = 1 To tot_Sheets
'Sheets(ActiveSheet.Index + 1).Activate
If s <> 0 Then Sheets(s).Activate
'----------------serial number on sheets----------------
'Check last row_number for a filled cell in sheets
Dim colName As String
colName = "A"
Dim LastCell As Integer
LastCell = Sheets(s).Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row
'insert a new column to assign serial number
Sheets(s).Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove
'----------------------
'assigning serial number
Dim j As Integer
Dim i As Integer
j = LastCell
For i = 1 To j
Sheets(s).Range("A" & i + 1).Value = i
Next i
Dim c As Integer
Sheets(s).Range("A" & i).Delete
'-----------------------
Next s
End Sub

Add a consecutive serial number into multiple sheets in a single excel file

The below code will help you to create a breaking serial numbers into multiple sheets in an excel macros. Suppose we have a number of four sheets into an excel workbook and each sheets containg few records. The scenario from below code will assign an unique numbers to all four sheets where the number is ending to the last record.

On above scenario sheet1 is having 6 records and assgnig with 1-6 serial number. Similarly for all sheet2,sheet3,sheet4 creating an unique number where the number is ending into the last sheet.

Note : This code is valid upto 4 sheets.

Sub AddSerialNumbers()
On Error GoTo Last
Dim colName As String
'----------------serial numb1r on sheet1----------------
'Check last row_number for a filled cell in sheet1
colName = "A"
Dim LastCell As Integer
LastCell = Sheet1.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

'insert a new column to assign serial number
Sheet1.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j As Integer
Dim i As Integer
j = LastCell
For i = 1 To j
Sheet1.Range("A" & i + 1).Value = i
Next i
Dim c As Integer
Sheet1.Range("A" & i).Delete
'----------------serial number on sheet2----------------
'Check last row_number for a filled cell in sheet2
Dim LastCell2 As Integer
LastCell2 = Sheet2.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

'insert a new column to assign serial number
Sheet2.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j1 As Integer
Dim i1 As Integer
j1 = LastCell2
For i1 = 1 To j1
Sheet2.Range("A" & i1 + 1).Value = i - 2 + i1
Next i1
Dim t As Integer
t = i - 2 + i1
Sheet2.Range("A" & i1).Delete
'----------------serial number on sheet3----------------
'Check last row_number for a filled cell in sheet3
Dim LastCell3 As Integer
LastCell3 = Sheet3.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

'insert a new column to assign serial number
Sheet3.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j2 As Integer
Dim i2 As Integer
j2 = LastCell3
For i2 = 1 To j2
Sheet3.Range("A" & i2 + 1).Value = t - 2 + i2
Next i2
Dim t1 As Integer
t1 = i2 - 2 + t
Sheet3.Range("A" & i2).Delete
'----------------serial number on sheet4----------------
'Check last row_number for a filled cell in sheet4
Dim LastCell4 As Integer
LastCell4 = Sheet4.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

 'insert a new column to assign serial number
Sheet4.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j3 As Integer
Dim i3 As Integer
j3 = LastCell4
For i3 = 1 To j3
Sheet4.Range("A" & i3 + 1).Value = t1 - 2 + i3
Next i3
Sheet4.Range("A" & i3).Delete
Last:
End Sub

Combine all Sheets into one Master Sheet in Excel Macros

Sub Consolidate_Data_From_Different_Sheets_Into_Single_Sheet()
'Procedure to Consolidate all sheets in a workbook
On Error GoTo IfError

'1. Variables declaration
Dim Sht As Worksheet, DstSht As Worksheet
Dim LstRow As Long, LstCol As Long, DstRow As Long
Dim i As Integer, EnRange As String
Dim SrcRng As Range

'2. Disable Screen Updating - stop screen flickering
'   And Disable Events to avoid inturupted dialogs / popups
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'3. Delete the Consolidate_Data WorkSheet if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("Consolidate_Data").Delete
Application.DisplayAlerts = True

'4. Add a new WorkSheet and name as 'Consolidate_Data'
With ActiveWorkbook
    Set DstSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    DstSht.Name = "Consolidate_Data"
End With

'5. Loop through each WorkSheet in the workbook and copy the data to the 'Consolidate_Data' WorkSheet
For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Name <> DstSht.Name Then
       '5.1: Find the last row on the 'Consolidate_Data' sheet
       DstRow = fn_LastRow(DstSht) + 1          
       '5.2: Find Input data range
       LstRow = fn_LastRow(Sht)
       LstCol = fn_LastColumn(Sht)
       EnRange = Sht.Cells(LstRow, LstCol).Address
       Set SrcRng = Sht.Range("A1:" & EnRange)
'5.3: Check whether there are enough rows in the 'Consolidate_Data' Worksheet
        If DstRow + SrcRng.Rows.Count > DstSht.Rows.Count Then
            MsgBox "There are not enough rows to place the data in the Consolidate_Data worksheet."
        GoTo IfError
        End If            
'5.4: Copy data to the 'consolidated_data' WorkSheet
 SrcRng.Copy Destination:=DstSht.Range("A" & DstRow)              
    End If
Next
IfError:

'6. Enable Screen Updating and Events
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

'In this example we are finding the last Row of specified Sheet
Function fn_LastRow(ByVal Sht As Worksheet)
    Dim lastRow As Long
    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
    lRow = Sht.Cells.SpecialCells(xlLastCell).Row
    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    fn_LastRow = lRow
End Function

'In this example we are finding the last column of specified Sheet
Function fn_LastColumn(ByVal Sht As Worksheet)
    Dim lastCol As Long
    lastCol = Sht.Cells.SpecialCells(xlLastCell).Column
    lCol = Sht.Cells.SpecialCells(xlLastCell).Column
    Do While Application.CountA(Sht.Columns(lCol)) = 0 And lCol <> 1
        lCol = lCol - 1
    Loop
    fn_LastColumn = lCol
End Function

Merge different excel workbooks into a single workbook into different sheets

Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook 
    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True) 
    If (vbBoolean <> VarType(fnameList)) Then 
        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
            Set wbkCurBook = ActiveWorkbook
            For Each fnameCurFile In fnameList
            countFiles = countFiles + 1
            Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
            For Each wksCurSheet In wbkSrcBook.Sheets
 countSheets = countSheets + 1
 wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
 Next
   wbkSrcBook.Close SaveChanges:=False
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If 
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

Delete Consecutive row in Excel Macros

Sub deleteeveryotherrow()
For x = 1 To 100 Step 1
Rows(x & ":" & x).Select
    Selection.Delete Shift:=xlUp
    Next x
End Sub

Select non-ascii characters more than 255 ascii code in SQL Server

;With cteNumbers as

(

    Select ROW_NUMBER() Over(Order By c1.Unique_ID_Column) as N

    From Your_Table_Name c1

)

Select Distinct Unique_ID_Column,Your_Column

From Your_Table_Name t

Join cteNumbers n ON n <= Len(CAST(Your_Column As NVarchar(MAX)))

Where UNICODE(Substring(Your_Column, n.N, 1)) > 255

OR UNICODE(Substring(Your_Column, n.N, 1)) <> ASCII(Substring(Your_Column, n.N, 1))

 order by 1

Select hidden Spaces which are not supporting by ASCII character in SQL Server.

;WITH cte AS

(

   SELECT 0 AS CharCode

   UNION ALL

   SELECT CharCode + 1 FROM cte WHERE CharCode <31

)

SELECT * FROM

   Your_Table_Name T

     cross join cte

WHERE

   EXISTS (SELECT Unique_ID_Column,Your_Column

        FROM Your_Table_Name Tx

        WHERE Tx.Unique_ID_Column = T.Unique_ID_Column

AND Tx.Your_Column LIKE '%' + CHAR(cte.CharCode) + '%'

and cte.CharCode>0

)

Execute a Table using PowerShell

Step 1 :
Open SSMS
Step 2 :
Right Click on your Database and start PowerShell.
Paste the below snippet.

Function SQL_CONN1 ($Query) {

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=Your_Server;Database=Your_DB;Integrated Security=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.Connection = $SqlConnection

$SqlCmd.CommandText = $Query

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$a=$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

$DataSet.Tables[0]  }

SQL_CONN1 “select * from PowerShell_Test”

How to open an excel sheet through a MS Access Button and do it modification.

Dim db As DAO.Database
Set db = CurrentDb()
      
Dim FileNameBase As String
FileNameBase = CurrentProject.path & "\YourExcelSheetName ".xlsx"
Dim strFileName As String
strFileName = Replace(FileNameBase, "[CurrentDate]", Format$(Date, "m-dd-yyyy"))

'---Exporting from Ms Access table to an Excel worksheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TableName1", strFileName, True, "Sheet_One_Name_You_Want"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TableName2", strFileName, True, "Sheet_Two_Name_You_Want"

'--------------Open that excel sheet and here you do all modification-----------------------------------
 Dim appExcel1 As Excel.Application
    Dim wbk1 As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim rng As Excel.Range
    
    Set appExcel1 = Excel.Application
    appExcel1.Visible = True
    Set wbk1 = appExcel1.Workbooks.Open(strFileName)
    
    appExcel1.ScreenUpdating = False
 ' -----------------------------------Formatting Sheet_One_Name_You_Want Sheet -------------------
     Sheets("Sheet_One_Name_You_Want").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Columns.AutoFit
    Range(Selection, Selection.End(xlToRight)).Columns.AutoFit
    Range(Selection, Selection.End(xlDown)).HorizontalAlignment = xlLeft
    Range(Selection, Selection.End(xlToRight)).HorizontalAlignment = xlLeft

    Range("A1:AK1").RowHeight = 30
    Range("A1:AK1").ColumnWidth = 15
    Range("A1:AK1").Font.Bold = True
    Range("A1:AK1").WrapText = True
    Range("A1:AK1").VerticalAlignment = Excel.Constants.xlCenter

'---------------Coloring-----------------
Range("A1:B1").Select
Selection.Interior.ColorIndex = 33

Range("C1").Select
Selection.Interior.ColorIndex = 27
Range("D1").Select
Selection.Interior.ColorIndex = 45

'--------------Change Data type to Currency Format for a Particular column--------------
Range("F2:F100").Select
Selection.Style = "Currency"
'------------------FreezePanes for a Particular column--------------
Range("C1").Select
wbk1.Application.ActiveWindow.FreezePanes = True
END Sub

Border Line for a filled cell in Excel VBA.

Once a time I was trying to create a report on excel through MS ACCESS vba. At that time I was really wanted to keep the border for all filled cells. I got a user define function from a browsing web page and it was really helpful.  You can modify this function according to your requirement.

Sub TheBorderLine()

With ActiveSheet.UsedRange.Borders

        .LineStyle =xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

 End With

    With ActiveSheet

        .Cells.Font.Size ="10"  

ActiveSheet.Cells.HorizontalAlignment = xlLeft

          End With

        Range("A1:S1").Select

    With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlCenter

    End With

  End Sub

Execution Process :
Call this function inside your button just like a - call TheBorderLine or just put TheBorderLine.
Make sure that your workbook is open while calling this function.

Select records where having minimum costs.

create table #temp
(
id int,
Name varchar(10),
Cost int
)

insert into #temp 
values
(1,'x',5),
(1,'z',4),
(2,'y',6),
(3,'x',2),
(3,'y',5),
(3,'z',8)
select * from #temp

Select tbl.* From #Temp tbl
Inner Join
(
  Select Id,MIN(cost) MinCost From #Temp Group By Id
)tbl1
On tbl1.id=tbl.id
Where tbl1.MinCost=tbl.Cost