Business/Data Analysis

10 years+ experience in Data Analysis using tools like: Power BI, Alteryx, Excel, Crystal Reports and SPSS as well as coding SQL for queries and data changes. Keywords: Sharepoint, SP workflows, Sp Designer, PowerShell, Regular Expressions

REST Query – Returns XML or JSON data such as
{“status”:”Ok”,”Data”:[{Obj1},{Obj2},{Obj3}]} which is an input option for reporting tools Power BI, Alteryx and data manipulation tools like SharePoint Designer.

AlteryxDisorganized data can be cleaned by using the regular expressions tool to divide crowded columns. The Join Tool was also used with a Candidate ID to join tables. With the absence of a unique ID a cascading “Not Found” is necessary. Following and ID join, First-Last-Company becomes a 2nd join, then by email become the 3rd join. This maximizes the match of candidates.

Power BI

Power BI

Power BI has a great query setup to preprocess data before it feeds graphs. It can take many somewhat repetitive columns and reduce the number of columns by pivoting just those columns to one. Let’s say a list is columned such as below.

AM-MAM-LAM-ABT-MBT-LBT-APMD-MPMD-LPMD-A
147148143

Digits are a representation of a part. In column names at the right of the dash, if M = Module, L = Lesson, A = Asset, then all we need are these 3 to be columns and a 4th Group column to be the identifier from the left side of the dash in headers.

ModuleLessonAssetGroup
147AM
148BT
143PMD

In the Query Editor, columns in the first table are pivoted up on its side to form two columns with names and digits. The column of names is split in two, divided by the – dash, leaving a column of M,L,As. Change this column data to be Module, Lesson, Asset, respectively. Transform this column back to 3 columns heading: Module, Lesson, Asset.  Power BI can compensate for lists of any type or databases, including REST queries.

 

Recent Companies

KPMG

Data Analyst – KPMG

ASME