Challenge

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

,Table,Row,Description,Special
1,application_data,SK_ID_CURR,ID of loan in our sample,
2,application_data,TARGET,"Target variable (1 - client with payment difficulties: he/she had late payment more than X days on at least one of the first Y installments of the loan in our sample, 0 - all other cases)",
5,application_data,NAME_CONTRACT_TYPE,Identification if loan is cash or revolving,
6,application_data,CODE_GENDER,Gender of the client,
7,application_data,FLAG_OWN_CAR,Flag if the client owns a car,
8,application_data,FLAG_OWN_REALTY,Flag if client owns a house or flat,
9,application_data,CNT_CHILDREN,Number of children the client has,
10,application_data,AMT_INCOME_TOTAL,Income of the client,
11,application_data,AMT_CREDIT,Credit amount of the loan,
12,application_data,AMT_ANNUITY,Loan annuity,
13,application_data,AMT_GOODS_PRICE,For consumer loans it is the price of the goods for which the loan is given,
14,application_data,NAME_TYPE_SUITE,Who was accompanying client when he was applying for the loan,
15,application_data,NAME_INCOME_TYPE,"Clients income type (businessman, working, maternity leave,…)",
16,application_data,NAME_EDUCATION_TYPE,Level of highest education the client achieved,
17,application_data,NAME_FAMILY_STATUS,Family status of the client,
18,application_data,NAME_HOUSING_TYPE,"What is the housing situation of the client (renting, living with parents, ...)",
19,application_data,REGION_POPULATION_RELATIVE,Normalized population of region where client lives (higher number means the clien t live s in more populated region),normalized
20,application_data,DAYS_BIRTH,Client's age in days at the time of application,time only relative to the application
21,application_data,DAYS_EMPLOYED,How many days before the application the person started current employment,time only rel ative to the app lication
22,application_data,DAYS_REGISTRATION,How many days before the application did client change his registration,time only re lative to th e ap plication
23,application_data,DAYS_ID_PUBLISH,How many days before the application did client change the identity document with whic h he a pplied for the loan,time only relative to the application
24,application_data,OWN_CAR_AGE,Age of client's car,
25,application_data,FLAG_MOBIL,"Did client provide mobile phone (1=YES, 0=NO)",
26,application_data,FLAG_EMP_PHONE,"Did client provide work phone (1=YES, 0=NO)",
27,application_data,FLAG_WORK_PHONE,"Did client provide home phone (1=YES, 0=NO)",
28,application_data,FLAG_CONT_MOBILE,"Was mobile phone reachable (1=YES, 0=NO)",
29,application_data,FLAG_PHONE,"Did client provide home phone (1=YES, 0=NO)",
30,application_data,FLAG_EMAIL,"Did client provide email (1=YES, 0=NO)",
31,application_data,OCCUPATION_TYPE,What kind of occupation does the client have,
32,application_data,CNT_FAM_MEMBERS,How many family members does client have,
33,application_data,REGION_RATING_CLIENT,"Our rating of the region where client lives (1,2,3)",
34,application_data,REGION_RATING_CLIENT_W_CITY,"Our rating of the region where client lives with taking city into account (1,2, 3)",
35,application_data,WEEKDAY_APPR_PROCESS_START,On which day of the week did the client apply for the loan,
36,application_data,HOUR_APPR_PROCESS_START,Approximately at what hour did the client apply for the loan,rounded
37,application_data,REG_REGION_NOT_LIVE_REGION,"Flag if client's permanent address does not match contact address (1=diffe rent, 0=same , at region level)",
38,application_data,REG_REGION_NOT_WORK_REGION,"Flag if client's permanent address does not match work address (1=differen t, 0=s ame, a t re gion level)",
39,application_data,LIVE_REGION_NOT_WORK_REGION,"Flag if client's contact address does not match work address (1=different , 0=sa me, at reg ion level)",
40,application_data,REG_CITY_NOT_LIVE_CITY,"Flag if client's permanent address does not match contact address (1=different , 0=sa me, at cit y level)",
41,application_data,REG_CITY_NOT_WORK_CITY,"Flag if client's permanent address does not match work address (1=different, 0 =same, at ci ty l evel)",
42,application_data,LIVE_CITY_NOT_WORK_CITY,"Flag if client's contact address does not match work address (1=different, 0= same, at cit y le vel)",
43,application_data,ORGANIZATION_TYPE,Type of organization where client works,
44,application_data,EXT_SOURCE_1,Normalized score from external data source,normalized
45,application_data,EXT_SOURCE_2,Normalized score from external data source,normalized
46,application_data,EXT_SOURCE_3,Normalized score from external data source,normalized
47,application_data,APARTMENTS_AVG,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
48,application_data,BASEMENTAREA_AVG,"Normalized information about building where the client lives, What is average (_AVG suffix ), mod us ( _MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
49,application_data,YEARS_BEGINEXPLUATATION_AVG,"Normalized information about building where the client lives, What is ave rage ( _AVG s uffi x), modus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
50,application_data,YEARS_BUILD_AVG,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
51,application_data,COMMONAREA_AVG,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
52,application_data,ELEVATORS_AVG,"Normalized information about building where the client lives, What is average (_AVG suf fix), modus (_MO DE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
53,application_data,ENTRANCES_AVG,"Normalized information about building where the client lives, What is average (_AVG suf fix), modus (_MO DE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
54,application_data,FLOORSMAX_AVG,"Normalized information about building where the client lives, What is average (_AVG suf fix), modus (_MO DE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
55,application_data,FLOORSMIN_AVG,"Normalized information about building where the client lives, What is average (_AVG suf fix), modus (_MO DE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
56,application_data,LANDAREA_AVG,"Normalized information about building where the client lives, What is average (_AVG suff ix), m odus ( _MOD E suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
57,application_data,LIVINGAPARTMENTS_AVG,"Normalized information about building where the client lives, What is average (_ AVG su ffix), mod us (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
58,application_data,LIVINGAREA_AVG,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
59,application_data,NONLIVINGAPARTMENTS_AVG,"Normalized information about building where the client lives, What is average (_AVG suffi x), modus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
60,application_data,NONLIVINGAREA_AVG,"Normalized information about building where the client lives, What is average (_AVG suffi x), mo dus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
61,application_data,APARTMENTS_MODE,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
62,application_data,BASEMENTAREA_MODE,"Normalized information about building where the client lives, What is average (_AVG suffi x), mo dus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
63,application_data,YEARS_BEGINEXPLUATATION_MODE,"Normalized information about building where the client lives, What is av erage (_AVG suff ix), modus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
64,application_data,YEARS_BUILD_MODE,"Normalized information about building where the client lives, What is average (_AVG suffix ), mod us ( _MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
65,application_data,COMMONAREA_MODE,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
66,application_data,ELEVATORS_MODE,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
67,application_data,ENTRANCES_MODE,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
68,application_data,FLOORSMAX_MODE,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
69,application_data,FLOORSMIN_MODE,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
70,application_data,LANDAREA_MODE,"Normalized information about building where the client lives, What is average (_AVG suf fix), modus (_MO DE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
71,application_data,LIVINGAPARTMENTS_MODE,"Normalized information about building where the client lives, What is average ( _AVG s uffix) , mo dus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
72,application_data,LIVINGAREA_MODE,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
73,application_data,NONLIVINGAPARTMENTS_MODE,"Normalized information about building where the client lives, What is averag e (_AV G suff ix), modus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
74,application_data,NONLIVINGAREA_MODE,"Normalized information about building where the client lives, What is average (_AV G suff ix), m odus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
75,application_data,APARTMENTS_MEDI,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
76,application_data,BASEMENTAREA_MEDI,"Normalized information about building where the client lives, What is average (_AVG suffi x), mo dus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
77,application_data,YEARS_BEGINEXPLUATATION_MEDI,"Normalized information about building where the client lives, What is av erage (_AVG suff ix), modus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
78,application_data,YEARS_BUILD_MEDI,"Normalized information about building where the client lives, What is average (_AVG suffix ), mod us ( _MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
79,application_data,COMMONAREA_MEDI,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
80,application_data,ELEVATORS_MEDI,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
81,application_data,ENTRANCES_MEDI,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
82,application_data,FLOORSMAX_MEDI,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
83,application_data,FLOORSMIN_MEDI,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
84,application_data,LANDAREA_MEDI,"Normalized information about building where the client lives, What is average (_AVG suf fix), modus (_MO DE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
85,application_data,LIVINGAPARTMENTS_MEDI,"Normalized information about building where the client lives, What is average ( _AVG s uffix) , mo dus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
86,application_data,LIVINGAREA_MEDI,"Normalized information about building where the client lives, What is average (_AVG s uffix) , modu s (_ MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
87,application_data,NONLIVINGAPARTMENTS_MEDI,"Normalized information about building where the client lives, What is averag e (_AV G suff ix), modus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
88,application_data,NONLIVINGAREA_MEDI,"Normalized information about building where the client lives, What is average (_AV G suff ix), m odus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
89,application_data,FONDKAPREMONT_MODE,"Normalized information about building where the client lives, What is average (_AV G suff ix), m odus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
90,application_data,HOUSETYPE_MODE,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
91,application_data,TOTALAREA_MODE,"Normalized information about building where the client lives, What is average (_AVG su ffix), modus (_M ODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
92,application_data,WALLSMATERIAL_MODE,"Normalized information about building where the client lives, What is average (_AV G suff ix), m odus (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
93,application_data,EMERGENCYSTATE_MODE,"Normalized information about building where the client lives, What is average (_A VG suf fix), modu s (_MODE suffix), median (_MEDI suffix) apartment size, common area, living area, age of building, number of elevators, number of entrances, state of the building, number of floor",normalized
94,application_data,OBS_30_CNT_SOCIAL_CIRCLE,How many observation of client's social surroundings with observable 30 DPD ( days p ast du e) d efault,
95,application_data,DEF_30_CNT_SOCIAL_CIRCLE,How many observation of client's social surroundings defaulted on 30 DPD (day s past due) ,
96,application_data,OBS_60_CNT_SOCIAL_CIRCLE,How many observation of client's social surroundings with observable 60 DPD ( days p ast du e) d efault,
97,application_data,DEF_60_CNT_SOCIAL_CIRCLE,How many observation of client's social surroundings defaulted on 60 (days pa st due ) DPD,
98,application_data,DAYS_LAST_PHONE_CHANGE,How many days before application did client change phone,
99,application_data,FLAG_DOCUMENT_2,Did client provide document 2,
100,application_data,FLAG_DOCUMENT_3,Did client provide document 3,
101,application_data,FLAG_DOCUMENT_4,Did client provide document 4,
102,application_data,FLAG_DOCUMENT_5,Did client provide document 5,
103,application_data,FLAG_DOCUMENT_6,Did client provide document 6,
104,application_data,FLAG_DOCUMENT_7,Did client provide document 7,
105,application_data,FLAG_DOCUMENT_8,Did client provide document 8,
106,application_data,FLAG_DOCUMENT_9,Did client provide document 9,
107,application_data,FLAG_DOCUMENT_10,Did client provide document 10,
108,application_data,FLAG_DOCUMENT_11,Did client provide document 11,
109,application_data,FLAG_DOCUMENT_12,Did client provide document 12,
110,application_data,FLAG_DOCUMENT_13,Did client provide document 13,
111,application_data,FLAG_DOCUMENT_14,Did client provide document 14,
112,application_data,FLAG_DOCUMENT_15,Did client provide document 15,
113,application_data,FLAG_DOCUMENT_16,Did client provide document 16,
114,application_data,FLAG_DOCUMENT_17,Did client provide document 17,
115,application_data,FLAG_DOCUMENT_18,Did client provide document 18,
116,application_data,FLAG_DOCUMENT_19,Did client provide document 19,
117,application_data,FLAG_DOCUMENT_20,Did client provide document 20,
118,application_data,FLAG_DOCUMENT_21,Did client provide document 21,
119,application_data,AMT_REQ_CREDIT_BUREAU_HOUR,Number of enquiries to Credit Bureau about the client one hour before appl icatio n,
120,application_data,AMT_REQ_CREDIT_BUREAU_DAY,Number of enquiries to Credit Bureau about the client one day before applic ation (exclu ding one hour before application),
121,application_data,AMT_REQ_CREDIT_BUREAU_WEEK,Number of enquiries to Credit Bureau about the client one week before appl icatio n (exc ludi ng one day before application),
122,application_data,AMT_REQ_CREDIT_BUREAU_MON,Number of enquiries to Credit Bureau about the client one month before appl icatio n (exc ludi ng one week before application),
123,application_data,AMT_REQ_CREDIT_BUREAU_QRT,Number of enquiries to Credit Bureau about the client 3 month before applic ation (exclu ding one month before application),
124,application_data,AMT_REQ_CREDIT_BUREAU_YEAR,Number of enquiries to Credit Bureau about the client one day year (exclud ing la st 3 m onth s before application),
Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.