Standard Operating Procedures

Data Entry tabs are any tab a user manually enter data. These tabs should have a logo and header, make use of the colors and fonts choosen above, and have an awareness to the need to focus on making capturing the data easy.

• A. Page Header With Branding
• B. Color code tab so the end user knows these sheet are the primary place to input data
• C. Ttable with data headers complete with relevant entry fields
• D. Logo for client branding
• Use of Font & Color Selections

This is an example of the placement of content and should not be interpreted as the required styling format for a data entry screen.

Reporting is any tab a user will go to gather info. It usually consists of calculations that pull data from the data entry screens. Logos and branding can be more prominent here as the goal of reports and dashboards are to "show off" the data and the brand to stakeholders.

• A. Page Header With Branding
• B. Date Filter
• C. Diminesion Filters / Slicers
• D. Visualization Area
• *Use of Font & Color Selections

This is an example of the placement of content and should not be interpreted as the required styling format for a reporting entry screen.

• Be Table Object
• Have Proper Headers
• Clean Clear Titles
• Table Must Be Named

Video Walkthrough

Calculations should be included and hidden in every manual entry for unless we are doing them in Power Query when applicable.

Requirements

• Clearly Named Columns
• Black Header With White Text
• Hidden From View

• Date

  • Start of Month: EOMONTH([Date],-1)+1
  • End of Month: EOMONTH([Date],0)
  • Weekending: [Date]+8-WEEKDAY([Date])
  • Year: Year([Date])
  • Weekday Name: TEXT([Date],"DDDD")

• String Functions

  • Len LEN(text)
  • Left LEFT(text, [num_chars])
  • Mid MID(text,start_num,num_chars)
  • Right RIGHT(text,[num_chars])
  • Text Join TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
  • Text Split TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

• Look Ups Functions

  • Vlookup VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match)
  • Hlookup HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • Xlookup XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • IndexINDEX(array, row_num, [column_num])
  • MatchMATCH(lookup_value, lookup_array, [match_type])
  • Index With Match [Date]+8-WEEKDAY([Date])

• IF Functions

  The IF function can evaluate multiple conditions, but it is less practical when performing several logical tests.
  • IF IF(Something is True, then do something, otherwise do something else)
  • IFERROR IFERROR(value, value_if_error)
  • SUMIF SUMIF(range, criteria, [sum_range])
  • AVERAGEIF AVERAGEIF(range, criteria, [average_range])

• IFS Functions

  The IFS function is more practical when performing several logical tests, as it is easier to read and requires less typing.
  • IFS IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)
  • SUMIFS SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • AVERAGEIFS AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• Uniue Include Per View
• Required Process

Video Walkthrough

Process

• Download Intellivega 2019 Excel Toolbar
• Enable "Trust access to the VBA project object model" in Macro Settings

Video Walkthrough

Process

• 1. Create File Path Load Query
• 2. Create Sharepoint Load Query
• 3. Create Dataset for Process
• 4. Ensure Sharepoint Connection Is Used When Delivering To Client
• 5. Disable Local File When Delivering To Client

Video Walkthrough

Process

• 1. Limit Parameter
• 2. Load SQL Tablke
• 3. Add Limit Filter To SQL
  let
mysql = if fnGetParameter("Limit Records")="" then "Select * From ProjectUpdates" else "Select * From ProjectUpdates Where CreatedOn >='" & fnGetParameter("Limit Records") & "'",
Source = Sql.Database("mysql.intellivega.com", "intelliv_Ampv3", [Query=mysql])
in
Source


Video Walkthrough

Process

• 1. Load Into Power Query
• 2. Remove Blank Rows
• 3. Tranpose
• 4. Remove Blank Rows
• 5. Transpose

Video Walkthrough

Data Cleaning Considerations

• 1. Data Strategy: What are the data errors? What is causing the data errors?
• 2. Can Identified Issues Be Corrected At Source?
• 3. Manage Duplicates
• 4. Append Missing Data
• 5. Clean Strings (If Applicable)
• 6. Set Data Types Zip Code Should Strings
• 7. Standarize Formats
• 8. Remove Errors
• 9. Remove Blanks

Video Walkthrough