Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

1.create pivot charts for all below mentioned questions & make an attractive dashboard out of it.

ans. Dashboard uploaded

2.Data Summary of All Female Client’s for Income and Loan Amount using New Functions (hint: use unique,sort,filter functions) if These functions are not supporting in your software please give a theoritical answer

ans.  unique(array,[by-col],[exactly-once])

        filter(array,include,[if-empty])

        sort(array,[sort-index],[sort-order],[by-col])

2.

Data Summary of All male Client’s for Income and Loan Amount
DSUM OF All male Client’s for Income	20318041019
DSUM OF All male Client’s for LOAN AMOUNT	64201050311
DAVERAGE OF All male Client’s for Income	193396.4822
DAVERAGE OF All male Client’s for LOAN AMOUNT	611095.1971

2.

Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.
DCOUNT FOR FLAG_DOCUMENT_2	24208072
DCOUNT FOR FLAG_DOCUMENT_3	24208072

3 Create VBA Code to convert selected data into pdf from applicant data

ans.  

Sub PrintToPDF()
'SUBROUTINE: PrintToPDF
'DEVELOPER: Ryan Wells


Dim ThisRng As Range
Dim strfile As String
Dim myfile As Variant

If Selection.Count = 1 Then
Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
Else
Set ThisRng = Selection
End If
'Prompt for save location
strfile = "Selection" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
strfile = ThisWorkbook.Path & "\" & strfile

myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")

If myfile <> "False" Then 'save as PDF
ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If

End Sub

3.Check Spelling mistakes in the workbook using VBA Code

Sub CheckSpelling() 
Dim ws As Worksheet
 Dim rng As Range For Each ws In Worksheets
 Set rng = ws.UsedRange
 rng.CheckSpelling
 Next ws
 End Sub