Microsoft Excel: How I leveraged Excel Microsoft Flow Power Query and Power BI to – BRK2066

THANK YOU FOR JOINING ME THIS MORNING TO HEAR ABOUT HOW I WORKED THROUGH MANY OF THE COMMON ANALYSIS PAY POINTS AND SUPPLY CHAIN USING MANY OF OUR MICROSOFT BUSINESS APPLICATIONS BEFORE GETTING STARTED, I JUST WANT TO GIVE YOU SOME CONTEXT OF MY BACKGROUND RIGHT NOW I’M IN THE MICROSOFT EXCEL PRODUCT TEAM BUT BEFORE THIS, I WAS DOING SUPPLY CHAIN PLANNING, ALSO WITH MICROSOFT. I DID THAT FOR ABOUT A YEAR AND A HALF. AND BEFORE THAT, I WAS DOING SUPPLY CHAIN CONSULTING. I’VE BEEN THERE OPERATING THE PLANNING PROCESS, GETTING ALL OF THE INPUNTS FROM ERL SV SOURCES, XWEL WAS AT THE SAME TIME MY BEST FRIEND AND MY BEST ENEMY, WORKS THE ENEMY FOR EXCEL. I WANT TO SHARE ALL OF THE DIFFERENT THINGS I DID TO MAKE MY LIFE BETTER AND TO GET TO BETTER BUSINESS OUTCOMES. JUST TO GET A SHOW OF HANDS, WHO WORKS IN OPERATIONS AND SUPPLY CHAIN? ALL RIGHT. WHO’S ALREADY USING TOOLS LIKE POWER QUERY? ALL RIGHT. AND POWER PIVOT? ALL RIGHT, COOL. I HOPE THIS WILL GIVE YOU ADDITIONAL TOOLS AND WILL FOR THOSE OF YOU WHO AREN’T USING IT YET SHOW YOU SOME OF THE VALUE THAT THERE IS TO USING THEM. SO, WE’RE GOING TO COVER SOME OF THE BUSINESS CONTEXT FOR OPERATION SUPPLY CHAIN, BUT THIS APPLIES TO MANY OTHER INDUSTRIES AS WELL. ANY INDUSTRY OR PROCESSING WHICH YOU ARE GETTING DATA, PROCESSING IT, TO ANOTHER STEP IN THE PROCESS, WE CAN LEVERAGE ANY OF THESE TOOLS TO HAVE A MORE STRUCTURE, MORE SCALABLE, AND MORE RELIABLE PROCESS. THEN WE’RE GOING TO SEE THE DIFFERENT APPLICATIONS OF THE TOOLS, AND THIS WILL BE, I’LL GO INDEPTH AND SHOW HOW TO BUILD IT IN THE TOOL ITSELF THERE ARE MANY OTHER SESSIONS FOR EACH OF THE TOOLS. IF YOU’RE INTERESTED IN SOME OF THE ADDITIONAL CONTENT YOU SHOULD DEFINITELY ATTEND SOME OF THE MORE INDEPTH SESSIONS. THE OUTCOME AND BEST PRACTICES AND RECOMMENDATIONS FROM SEEING THIS FIRST HAND. IN TERMS OF BUSINESS CONTEXT, WE HAVE A PROCESS THAT HAS HIGH COMPLEXITY, HIGH CADENCE. RUNNING PROCESSES DAILY OR WEEKLY WHERE THERE ARE MULTIPLE PARTNERS INVOLVED. CAN BE INTERNAL COLLABORATORS, MARKETING TEAMS, SALES TEAMS, LOGISTICS TEAMS AS WELL AS EXTERNAL COLLABORATORS IN THE FORM OF SUPPLIERS OR DIFFERENT VENDORS THAT ARE DOING PART OF THE — PART OF THE ECOSYSTEM, AND DOING SPECIFIC MARKETING WORK OR ANY KIND OF PROCESSES. THEN BUSINESS DYNAMICS THAT ARE CONSTANTLY CHANGING, FOR INSTANCE IN THE SUPPLY CHAIN WE HAD SALES FORECASTED FOR A LAUNCH, AND ONCE THE PRODUCT WENT OUT TO MARKET IT COULD BE LIKE WAY UP OR WAY DOWN COMPARED TO WHAT THE FORECAST WAS. THESE ARE INPUTS THAT ARE CONSTANTLY CHANGING. AND THE I. T. FRONT, YOU HAVE POOLS THAT ARE LIKE A DIME A DOZEN. YOU HAVE TO PULL FROM DIFFERENT TOOLS YOU HAVE TO COME UP WITH DIFFERENT INITIATIVES NOT ONLY MULTIPLE BUT CHANGING THE TOOLS YOU ARE USED TO. SO THIS CREATES AN ENVIRONMENT WHERE YOU HAVE MANUALLY UPDATE SPREADSHEETS WHERE THERE IS ANALYSIS THAT NEED TO BE DONE FOR X OR Y SESSION, OR THE WEEKLY LEADERSHIP MEETING, AND THE LIST GOES ON. AND THEN THIS TURNS INTO A REACTIVE APPROACH, YOU AREN’T REALLY THINKING AHEAD ABOUT THE BUSINESS AND WHAT IS GOING TO BE COMING. BUT IT IS MORE THE FIRES THAT ARE HAPPENING ALL THE TIME THIS ISN’T SCALABLE, THIS IS ERROR-PRONE, IT IS LIMITING YOUR ATTENTION TO THE SHORT TERM, TO THE URGENT AND NOT NECESSARILY THE IMPORTANT PART OF YOUR JOB. UNFORTUNATELY, IT CREATES

AN INTENSE AND UNPREDICTABLE WORKLOAD THAT IS NOT IDEAL FOR YOUR WORK-LIFE BALANCE. HOPEFULLY BY THE END OF THE SESSION YOU WILL HAVE LIKE GOOD TOOLS TO SPEND LESS TIME IN EXCEL, AS MUCH AS I LOVE THE PRODUCT IF YOU CAN AUTOMATE PART OF WHAT YOU’RE DOING THAT WILL BE A GREAT OUTCOME WITH THIS, HERE’S A TYPICAL PLANNING PROCESS WHERE YOU HAVE SEVERAL TEAMS THAT ARE GIVING INFORMATION BETWEEN EACH OTHER. CAN YOU EXTRAPOLATE THIS TO MANY OTHER PROCESSES AND FUNCTIONS. IN THIS CASE WE HAVE A DEMAND TEAM THAT IS GIVING INPUT TO THE FINISH GOOD TEAM. THEY’RE PUTTING TOGETHER THEIR PLAN. AND THEN THEY PASS IT ON TO A MATERIALS TEAM THAT IS EXPLODING THAT INTO THE INDIVIDUAL COMPONENT. WITHOUT GOING INTO TOO MUCH DETAIL HERE, I WANT TO GO OVER THE KEY ELEMENTS THAT YOU CAN DO IN ORDER TO IMPROVE THE PROCESS AND IN ORDER TO GET AHEAD OF THE GAME AND HAVE THE BEST BUSINESS OUTCOME. THE FIRST THING IS, YOU HAVE TO PULL INFORMATION ALL THE TIME FROM DIFFERENT SOURCES FROM BET A CUBES, FROM FLAT FILES, FROM EXCELS. LEVERAGE AS MUCH AS YOU CAN THE DATA PREP AUTOMATION THIS POWER QUERY IS THE BEST TOOL FOR THAT. IT WORKS BOTH IN EXCEL AND POWER B.I. THAT IS A FAMILIAR ENVIRONMENT ONCE YOU LEARN IT. ONCE YOU HAVE THE DATA, COMBINING IT AND CREATING MODELS WITH POWER PIVOT, WILL BE EXTREMELY POWERFUL. THIS IS THE UNDERLYING TECHNOLOGY FOR POWER B. I. AS WELL. AND THEN AUTOMATING YOUR WORKFLOW. WHENEVER YOU HAVE FOR INSTANCE A SUPPLIER THAT GIVES YOU BACK CONFIRMATIONS, MANY OF THESE HAPPEN IN STRUCTURE TOOLS, IN DYNAMICS, IN S. A. P. , IN DIFFERENT STRUCTURE TOOLS SOMETIMES DEPENDING ON THE SEMIER, MAY NOT BE THERE YET, IT MAY BE A TEST RUN. BUT YOU GET THIS FREQUENT FLOW OF INFORMATION. FOR THEM FLOW IS A GREAT TOOL TO AUTOMATE THE WORKFLOWS. FINALLY, SHARING INSIGHTS WITH POWER B. I. IN A VERY SCALABLE WAY WHERE YOU DON’T NEED TO WORRY ABOUT PEOPLE, LIKE ADDING A ROW OR ADDING A COLUMN OR A SHEET IN THE SPREADSHEET. THIS IS A MODEL THAT PEOPLE CAN INTERACT WITH, CUSTOM VIEW, AND HAVE THEIR OWN INFORMATION, VERY POWERFUL AND SCALABLE. I’M GOING INTO THE DETAIL OF EACH OF THESE STARTING WITH AUTOMATING THE DATA PREP WITH POWER QUERY. STOW, MANY OF YOU MAY BE FAMILIAR — SO MANY OF YOU MAY BE FAMILIAR WITH THE WORKFLOW WHERE THE ONE SENDS YOU AN E-MAIL, IT HAS A FLAT FILE, HOPEFULLY IN THE SAME FORMAT THAT THEY SENT IT IN LAST WEEK. NOT NECESSARILY THE CASE. AND YOU JUST GO IN TO EXCEL, OPEN, COPY, PASTE IT INTO THE SPREADSHEET, IT CAN BE BETWEEN TWO TIMES A WEEK AND 20 TIMES A WEEK DEPENDING ON YOUR PROCESS AND FUNCTION. HERE, UNFORTUNATELY, THE PERSON WHO SENT IT ALREADY DECIDED TO PIVOT IT. SO IF YOU EVER TRY TO DO THIS BEFORE POWER QUERY, YOU KNOW THE PAIN. I REMEMBER I HAD A MACRO THAT DID IT. THE THING IS, IT NEEDED TO BE A PERFECTLY STRUCTURED DATA SET FOR IT TO WORK. ONCE YOU GET TO AUTOMATE THE PROCESS, ALL IT TAKES IS YOU HAVE YOUR DATA SPREADSHEET, YOU CLICK REFRESH, ALL OF THE HEAVY LIFTING IS DONE BY EXCEL, IT CONNECTS TO THE DIFFERENT DATA SOURCES, QUERIES AND READS YOUR DATA BACK INTO THE GRID ALREADY PIVOTED. SO, THE TECH NOMG BEHIND THIS IS POWER QUERY IT IS DATA CONNECTIVITY AND DATA PREPARATION TOOL THAT POWERS BOTH EXCEL AND POWER B.I. TYPICAL WORKFLOW IS CONNECTING TO YOUR DATA, THERE ARE A MULTITUDE OF SERVICES THAT ARE SUPPORTED, AND THIS CONTINUES TO GROW IN TERMS OF CONNECTORS IT CAN BE EXCEL FILES, IT CAN BE CSV FLAT FILES, IT CAN BE SQL SERVERS, IT CAN BE DATA CUBES, ACCESS DATABASE IF YOU’RE STILL USING THOSE. AND

IT ALLOWS TO TRANSFORM THE DATA THINK OF IT AS ALL OF THE STEPS YOU’RE MANAGING TODAY. TO COMBINE WITH POWER PIVOT WHICH WE WILL COVER AND SHARE ON IT POWER B. I. THE INITIAL SETUP MAY LOOK LIKE LOTS HAVE STEPS BUT YOU’LL DO IT JUST ONCE THEN IT CONTINUES TO WON. USING THE FILES FROM SHAREPOINT CONNECTION THIS ENABLES ME TO GO TO TEAMS, SHAREPOINT, COPY THE LIBRARY LOCATION IT HAS THE FULL FOLDER, MAKE SURE IT IS JUST THE LIBRARY. IT IS GOING TO PULL ALL OF THE CONTENTS IF YOU HAVE LESS OR OTHER OBJECTS LIVING IN SHAREPOINT, IT WILL PULL ALL OF THOSE. THEN ONCE IT CONNECTS TO THE SOURCE, GOING TO SHOW YOU THE CONTENT OF IT. IT HAS ALL OF YOUR FILES ORGANIZED BY FOLDER IN THIS CASE WHAT I’M GOING TO DO IS LIKE TWO BIG STEPS. ONE IS FILTER DOWN TO THE RIGHT FILES. HERE I’LL CHOOSE ONE EXTENSION, PULL EXCEL FILES, THAT’S HOW I GET THE DATA FILLINGTER DOWN BY EXTENSION. PULL THE FILES FROM THE LAST FOUR DAYS THIS IS A WEEKLY PROCESS IN WHICH I’M JUST FILTERING DOWN THE FILES TO DO SO, I GO TO THE DATE MODIFIED, AND YOU CAN CHOOSE SPECIFIC FILES THEN IT WILL BE HARD-CODED. IN THIS CASE I’LL SAY IN THE PREVIOUS THREE DAYS. OR TODAY. I ALSO WANT TO INCLUDE FILES THAT WERE MODIFIED. WITH THIS, I GET DOWN TO FEWER FILES, JUST THE ONES I WANT TO TAKE. BUT I SEE THAT HERE I HAVE THE FIRST FILE IS MY PROJECTED AVAILABILITY, WHICH IS ALREADY PART OF THE PROCESS DATA GOING TO FILTER DOWN TO THE FOLDER WITHIN THAT SHAREPOINT LIBRARY, THE INPUT FILES. THAT’S GOING TO BRING IT DOWN TO THAT DATA SET THAT I WANT. THIS LOOKS DIFFERENT IF YOU’RE KICKING TO A SQL SERVER, ANY OF THE OTHER OPTIONS. BUT THE RATIONALITY IS SIMILAR. YOU ARE JUST FILTERING DOWN TO THAT SUB SET OF DATA. THE NEXT STEP YOU HAVE THESE ATTRIBUTES, IF YOU AREN’T FAMILIAR, THIS IS NOT THE GRID ITSELF, THIS IS THE EDITOR. YOU HAVE ADDITIONAL OBJECTS ONCE I EXPAND THE CONTENT I CAN SEE THE SAMPLE FILE FOR THE CONTENT THAT I FILTER DOWN TO. ONCE I SELECT THE DATA I WANT TO PULL, THE TABLES FROM THE SPREADSHEETS, POWER CREATOR CREATES ADDITIONAL INTERMEDIARY QUERIES THAT ARE FUNCTIONS FOR THE DATA TO BE PROCESSED. YOU SEE THEM ON THE LEFT GROUPED UNDER A SAMPLE QUERY, AND THAT’S GOING TO BE CALLED, THAT’S THE SECOND STEP. PROCESSING YOUR DATA, ONCE YOU HAVE FILTERED IT DOWN. I MENTIONED THAT, THE SPREADSHEET HERE WAS PIVOTED. IN POWER CREATE, ALL YOU HAVE TO DO IS SELECT THE COLUMNS, THEN RIGHT CLICK OR GO TO THE TRANSFORM TAB. AND THERE’S AN OPTION WHICH IS UNPIVOT COLUMN THIS WILL CREATE WELL FOR EACH OF THE COLUMNS AND GET THE DATA IN THE FORM THAT I NEED IT. I NAME MY QUERY, AND THEN I LOAD IT INTO THE GRID, I’LL TALK A BIT LATER ABOUT LOADING IT INTO THE DATA MODEL AND HOW THAT’S DIFFERENT. MANY PEOPLE ASK WHEN POWER CREATOR, WHEN POWER PIVOT, I’LL TOUCH ON THAT. HERE I HAVE MY DATA IN THE DPRID. NEXT TIME YOU DON’T TO HAVE GO THROUGH EACH OF THESE STEPS, CLICK REFRESH OR AUTOMATE THE REFRESHES IN POWER B. I. AND GET THE DATA IN THE FORM THAT YOU NEED. THIS COULD BE ADDING ADDITIONAL FIELDS, ADDITIONAL COLUMNS, OR TRIMMING SOME OF THE LINES IF YOU’RE FILTERING DOWN TO A SPECIFIC THE TIME FLAME. SO, WITH ONCE YOU HAVE THE KATE A — THE DATA, THE NEXT THING IN THE PROCESS WAS COMBINING IT. MANY PEOPLE, WHETHER THEY FIRST START, AND THIS WAS MYSELF, WHEN I FIRST LEARNED POWER CREATE, THIS

IS AMAZING, I’LL STOP DOING THE LOOKUPS FOREVER AND HAVE A SINGLE TABLE WHERE I HAVE EVERYTHING JOINED YOU QUICKLY HIT A LIMIT WHEN YOU NEED TO JOIN TABLES THAT ARE NOT LIVING AT THE SAME LEVEL OF AGGREGATION OR DON’T HAVE THE SAME STRUCTURE YOU CANNOT HAVE A SINGLE LINE THAT TIES TO BOTH OF THEM. HERE’S WHERE POWER PIVOT COMES REALLY HANDY ALLOWS YOU TO JOIN DIFFERENT TABLES AND AVOID HAVING TO MANUALLY PULL THE DIFFERENT KEYS INTO EXCEL. A TYPICAL PROCESS LOOKS LIKE THIS YOU HAVE YOUR DATE ARC THE ONE WE PULLED BEFORE, BUT YOU NEED TO ADD YOUR SPECIFIC CALENDAR. YOU PROBABLY HAVE FOUR OR FIVE CALENDAR THAT MAY HAVE VERY SPECIFIC FISCAL YEAR END, AND SO IT’S A CUSTOM CALENDAR FOR YOUR PRODUCTS, YOUR PROGRAM IN THE PAST YOU WOULD JUST LOOK UP EVERYWHERE. IT USED TO TAKE FOREVER NOW IT’S FASTER IN EXCEL. WE MADE THIS FUNCTION ORDERS OF MAGNITUDE FASTER, MORE THAN 20 TIMES FASTER THAN IT USED TO BE. IF YOU ARE STILL DOING IT, YOU HAVE ADVANTAGE, BUT HERE I WILL SHOW YOU HOW NOT TO DO IT. YOU WOULD BRING YOUR DATA TOGETHER THROUGH THE LOOKUPS, THEN START CREATING YOUR REPORTS OR PIVOTS DOES THIS LOOK LIKE — YOU ARE DOING, OR WERE DOING BEFORE? I SEE SOME NODS. WITH POWER PIVOT, YOU CREATE A DATA MODEL, IF YOU WORKED WITH DATA DAYS BASES BEFORE, THIS LOOKS LIKE A RELATIONAL DATABASE. YOU DON’T HAVE TO PERFECTLY ESTABLISH IT FROM THE START AND HAVE EACH OF YOUR TABLES ALREADY BUILT. AT THE END OF THE DAY YOU’RE PULLING DATA FROM MANY DIFFERENT SOURCES ALLOWS YOU TO CREATE THAT MODEL AND THE BEST PRACTICES FOR THOSE DATA MODELS ALSO APPLY HERE. IDEALLY YOU HAVE A TABLE THAT HAS MEASUREMENTS AND SEPARATE TABLES WITH DIMENSIONS AND TIED TOGETHER. WE HAD A SESSION YESTERDAY WITH KEN PALLS, IT WILL BE LISTED AT THE END, THIS WILL BE POSTED AFTER THE CONFERENCE IT’S THIS IN MUCH MORE DEPTH AND BEST PRACTICES AROUND THIS. ONCE YOU HAVE YOUR DATA MODEL SET UP, YOU HAVE THE DATA MODEL FIELDS YOU CAN SEE THE CONSOLIDATED DATA MODEL, THEN CALENDAR DIMENSION TABLE AND PROGRAM CUE DIMENSION TABLE. JUST TO GO THROUGH THE PROCESS OF BUILDING IT. THIS CAN BE A STAR OR SNOWFLAKE CONFIGURATION, MANY DIFFERENT TABLES JOINING EACH OTHER AND WHERE YOU PIVOT ON DIFFERENT ELEMENTS. SO, HERE HOW DO WE PUT IT TOGETHER? THE FIRST THING IS GETTING YOUR DATA INTO THE GATE A MODEL. SO IF YOU CREATED THE POWER QUERY, QUERY TO FILTER DATA, THE WAY TO DO IT IS YOU CHECK THE ADD TO DATA MODEL BOX. THIS WILL LOAD IT INTO YOUR POWER PIVOT DATA MODEL. THERE ARE OTHER SOURCES, YOU HAVE THE SKEW AND CALENDAR, I’M GOING TO ASSUME THEY LIVE IN A SPECIFIC FILE. YOU CAN ALSO PULL THEM THROUGH POWER QUERY INTO THE DATA MODEL. THERE’S AN OPTION TO USE THEM DIRECTLY FROM THE GRID, REFER TO AN EXCEL TABLE BUT IT IS MUCH CLEANER FOR YOU TO USE POWER QUERY BEFORE YOU GO INTO POWER PIVOT. THAT OPTION, SEEMS TO BE BECAUSE IT’S ONE FEWER STEP THIS WILL ALLOW YOU TO SCALE THE DATA IF TOMORROW I. T. SAYS, OKAY, HERE, WE HAVE THIS DATA CUBE THAT HAS YOUR SKEWS, YOU JUST POINT THE POWER QUERY TO THAT SQL SERVER INSTEAD OF HAVING TO CREATE A NEW CONNECTION FROM SCRATCH. AND RESET ALL OF THOSE RELATIONSHIPS. ADDING THE DATA INTO POWER QUERY. RIGHT FROM EXCEL. FORMATTING

IT THE RIGHT WAY. IF YOU END UP WITH AN ABC, 123, IT’S GENERIC FIELD, ONCE IT’S LOADED INTO THE DATA MODEL IT WON’T BE RESPECTIVE LIKE THAT SPECIFIC FORMATTING EVEN IF IT LOOKS LIKE IT’S A DATE IT’LL JUST SHOW NUMBERS. LIKE THE INTE JER WAY OF SHOWING THE DATES. ONCE WE HAVE IT IN THE DATA MODEL, I CLICK IN THE POWER QUERY EDITOR IN THE DATA TAB, AND THIS ALLOWS ME TO GO INTO THE DIAGRAM VIEW. IT HAS THE TABLES INITIALLY, AND I START CREATING THOSE RELATIONSHIPS. LIKE I SAID, YOUR DIMENSION TABLES, WHICH ARE THE PROGRAM CUES AND THE CALENDAR TABLE, SHOULD BE HIKE ONE TOO MANY YOU JOIN THAT INTO YOUR MEASUREMENTS TABLE. WUNTS I HAVE THAT, I FINISH THE SETUP, I ADD A PIVOT TABLE AND I HAVE ALL OF THOSE FIELDS LISTED I CAN PIVOT THEM DIRECTLY WITHOUT BEING WORRIED ABOUT THE LOOKUPS OR REDOING IT EVERY TIME THE DATA CHANGES. SO, HERE YOU SEE THE TABLES A LITTLE BIT DIFFERENT THAN THE PREVIOUS ONE, SHOWS REPEATED PROGRAM AND SKEW. IDEALLY ONCE YOU HAVE IT SET UP YOU CAN HIDE IT FROM CLIENT TOOLS AND YOU HAVE THIS CLEAN VERSION IN WHICH THERE’S NO RISK OF TAKING THE DIMENSION FIELDS FROM THE DATA TABLE. HERE THE DATA TABLE ONLY WITH MEASUREMENTS AND ANALYZING TABLES ONLY WITH THE DIMENSION SO, THAT IS THE STEP WITH POWER PIVOT. YOU HAVE LIKE VERY POWERFUL TOOLS TO AUTOMATE THE WORKFLOWS WHEN I STARTED IMPLEMENTING THIS, WHEN I FIRST JOINED THE PLANNING TEAM I WAS SPENDING PROBABLY 10, 15 HOURS A WEEK IN EXCEL. ONCE I STARTED AUTOMATING THESE, I WAS DOWN TO SEVEN HOURS AND ABLE TO FOCUS ON NOT WHAT IS THE DATA SHOWING, THE SDAT A IS WRONG, I NEED TO PULL THESE LOOKUPS, BUT FOCUSING ON WE HAVE THIS DECREASING DEMAND, HOW DO WE REACT TO IT. INCREASING COST FOR THE SPECIFIC PROGRAM. LESS TIME CRUNCHING DATA AND MORE TIME THINKING ABOUT THE BUSINESS PROBLEMS. INSTEAD OF USING THIS, ADD THIS DATA, THIS DATA, SO ON, I WAS LEVERAGING FLOW BASICALLY ALLOWS YOU TO CREATE AUTOMATED WORKFLOW BETWEEN YOUR APPS AND SERVICES AND GET NOTIFICATIONS, AND CREATE A BUNCH OF ACTIONS WHENEVER ANY OF THOSE TRIGGERS HAPPEN. THERE ARE MANY OTHERS. THERE ARE SEVERAL THIRD PARTY CORRECTORS. YOU CAN CONNECT INTO — YES. AZURE, TWITTER, EXTERNAL SERVICES AS WELL. WHENEVER THERE IS A TWEET YOU ANALYZE THE SENTIMENT FOR THAT TWEET. YOU STORE IT IN A SPECIFIC DATABASE, CREATE A SALES LEAD IN YOUR CRM AND SO ON. SO, THIS IS A TOOL THAT I LOVE REALLY NICE THING, THERE’S NO CODING REQUIRED. THIS JUST REQUIRES YOU TO KNOW WHERE YOUR DATA IS LIVING AND WITH A FEW CLICKS CAN YOU SET IT UP. THAT IS WHAT WE’RE GOING TO DO NEXT. HERE I GO TO FLOW. MICROSOFT

com AND START WITH AN YOU A SO MATED FLOW. I WILL NAME IT AS SAVE PRODUCTION PLAN. INSTEAD OF TAKING THE E-MAIL THAT I WAS GETTING FROM THE PLANNERS, I’LL SAVE IT AUTOMATICALLY INTO THAT SHAREPOINT LIBRARY WE WERE PULLING FROM POWER QUERY. MY TRIGGER IS WHEN AN E-MAIL ARRIVES TO MY INBOX, I’M GOING TO FILTER BY WHETHER IT HAS AN ATTACHMENT OR NOT. I ONLY WANT TO PULL E-MAILS WITH ATTACHMENTS AND I’M GOING TO FILTER DOWN BY SUBJECT LINE WHICH IS PRODUCTION PLAN. THIS COULD BE DISTRIBUTION LIST, FILTER OUT IF IT’S SENT TO A SPECIFIC FILTER LIST OR FROM A SPECIFIC PERSON OR DOMAIN. THE NEXT STEP ARE THE ACTIONS. I’LL SAVE IT INTO SHAREPOINT, I’M CREATING A FILE EVERY TIME THAT THE E-MAIL ARRIVES AND IT SATISFIES THE CRITERIA THAT I SENT. I RECEIVE MY MOST RECENT SHAREPOINT LIBRARIES, I PULL THE RIGHT LIBRARY AND NAVIGATE TO THE RIGHT FOLDER. IN THIS CASE, IT’S LIVING UNDER THE SHARED DOCUMENTS, AND THE PRODUCTION PLANS. SO, ONCE I SELECT THE PLAN HERE, INPUT PLANS, I NEED TO DETERMINE WHAT I’M GOING TO NAME THE FILE AND WHAT IS GOING TO GO INTO THE FILE. I’LL USE THE SAME NAME THAT IT WAS CREATED BUT I COULD SAVE IT BY DATE SOURCE, USING THE PERSON WHO SENT THE E-MAIL, AND THEN THE CONTENT OF THE FILES WILL BE THE ATTACHMENT OF THE FILES IN IS TO SET IT UP HERE I CAN TEST IT TEST THE FLOW, IT’S GOING TO BE LISTENING TO THOSE EVENTS. THIS IS MY SHAREPOINT LIBRARY USING THE ONEDRIVE CLIENT. AND WE SEE THAT ONCE THE E-MAIL ARRIVES, IT’S SAVED DIRECTLY. NOW I’M GETTING THE E-MAIL, HEY, THIS IS THE BOND, STAVED IT AS A WORD FILE. THIS IS USEFUL FOR CASES IN WHICH YOU WANT TO AUTOMATE APPROVALS AND YOU CAN SET WHENEVER A FILE IS CREATED. NEEDS TO GO TO A PERSON IN THE ORGANIZE OR YOUR MANAGER ONCE THAT APPROVAL IS RECEIVED THE FILE CAN BE SAVED OR CONNECTED TO A DYNAMICS TRIGGER, APPROVES A P O. , PURCHASE ORDER, SO ON. HERE IS A BASIC ONE, EXTREMELY USEFUL TO AUTOMATE THESE STEPS. BRING INTO THE PROCESS THE LAST SECTION I WANT TO COVERERS SHARING THIS DATA AND SHARING THE INSIGHTS WITH THE REST OF THE ORG. AUTOMATING WORKFLOWS NOW CLEARING IT OUT TO EVERYONE ELSE. HOW DO WE GET FROM THIS DATA MOD EMTHAT I HAVE IN EXCEL INTO A REPORT OR A DASHBOARD THAT I CAN SHARE OUT WITH THE ORGANIZATION GET EVERYBODY SELF SERVING WITH B. I. VERY POWERFUL CONNECTOR AND POWER B. I. THAT ALLOWS YOU TO EXPORT THAT MODEL DIRECTLY. THIS IS THE MODEL I SHOWED YOU BEFORE. FROM EXCEL YOU GO TO PUBLIC. POWER B I. PUBLISHING OPTION. UPLOAD YOUR WORKBOOK TO POWER B. I. , IT WILL BE AN EMBELDED VERSION OF THE FILE THE OTHER ONE IS EXPORTING THE WHOLE MODEL. ALL OF THE CONNECTIONS, THE POWER QUERIES, WILL BE IN POWER B. I. AND YOU WILL BE ABLE TO JUST

SET A REFRESH FREQUENT FOR THAT AND SHARE IT WITH OTHERS. ONCE IT IS COMPLETED IT GETS BUSINESS IN EXCEL SHOWING THAT THE MODEL HAS BEEN UPLOADED. AND HERE I GO INTO THE POWER B. I. ONLINE EDITOR AND START USING THE DATA, WHATEVER WAY I WANT, SO I CAN CREATE A BAR CHART WHERE I’M GOING TO SHOW THE PROGRAMS AND THE QUANTITIES. SO THIS, WHAT IS REALLY NICE ABOUT IT, YOU HAVE ANOTHER ANOTHER OF THE STEPS IS THE WEEKLY REPORT. I WENT THROUGH THE PROCESS AND SEND IT TO THE DISTRIBUTION LIST, MAKE SURE I ADDED THE RIGHT PERSON WHO JUST JOINED THE ORGANIZE AND SO ON. AND AFTER I PUT THIS TOGETHER, IT WAS JUST HERE’S THE LINK, THAT WAS ONE FEWER STEP. DOWN TO EVEN LESS TIME ON THAT TURN AND CONSTANT REPEATING, LIKE, WORK THAT NEEDS TO BE DONE. TO BE ABLE TO FOCUS ON, LIKE, ACTUAL BUSINESS PROBLEM. WHAT THIS LED TO BEING ABLE TO FOCUS ON HIGH IMPACT PROBLEMS INSTEAD OF DATA PROCESSING PROBLEMS WE WERE PAYING MORE ATTENTION TO ALL OF THESE METRICS AND IN THE CASE OF SUPPLY CHAIN PLANNING WAS LOWER INVENTORY, FEWER STOCKOUTS, HIGHER SERVICE LEVELS. MORE ACCURACY AND VELOCITY. I CAN HAVE THE REPORT UPDATED EVERY DAY INSTEAD OF EVERY WEEK. I CAN HAVE A CYCLE THAT IS MUCH FASTER THAN MUCH MORE REACTIVE REDUCE WORKLOAD FROM THOSE TASKS DOING OVER AND OVER. A QUESTION THAT I GET OFTEN IS WHY DON’T I DO THIS USING DYNAMICS OR THIS ERP THAT I HAVE IN MY COMPANY. YOU CAN LEVERAGE THAT. WE NEED THIS ADDITIONAL WORKFLOW. THOSE TOOLS ARE NOT AGILE AND NIMBLE ENOUGH TO BE ABLE TO ADAPT TO MANY OF THE BUSINESSES RE-IMPLEMENT IT. HERE YOU HAVE THE FLEXIBILITY TO DO YOUR OWN WORKFLOW PART OF THE DIGITAL TRANSFORMATION, GREAT STEPPING STONE TO GET RESULTS MELDLY. AND TO BE ABLE TO TAKE CONTROL OF THE PROCESSES IN THE SHORT TERM AND NOT DEPEND ON I. T. TO PUT THEM TOGETHER. AS A BUSINESS USER, AS AN ANALYST, WE NEED TO THINK DIFFERENT ABOUT DATA TASKS AND ABILITY. IT’S DIFFICULT TO GET A REQUEST, RUNNING THE AD HOC ANALYSIS. TAKE A SECOND AND SAY DO I NEED ON DO THIS NEXT WEEK, THE ANSWER IS YES, I’LL SET UP A POWER QUERY. FOR THE NEXT, LIKE, 35, 50 WEEKS WHERE THE REPORT IS USEFUL IT WILL BE TAKEN CARE OF. DATA QUALITY WX MUCH MORE IMPORTANT, YOU AREN’T GOING TO BE HANDLING AND MANIPULATING IT. GREAT TO SET UP ADDITIONAL QUERIES FOR VALIDATION I SET UP MASSIVE SPREADSHEETS THAT WERE JUST VALIDATIONS ON DO I HAVE, LIKE, ALL OF THE PLAN CODES, DO I HAVE ALL OF THE WEEKS IN THE HORIZON, SOMETIMES THE PLANNING TEAM WITH THINGS LIKE THE LAST THREE MONTHS, DIFFERENT THINGS. IT IS IMPORTANT TO HOLD YOUR PARTNER TEAMS ACCOUNTABLE TO IT. THIS JUST ALLOWS YOU TO DO IT IN A MUCH MORE SCALABLE WAY CHECK AND REMEMBER TO CHECK EVERY TIME THOSE LAST THREE MONTHS OF THE DATA. HOOR THE OWNERSHIP MODEL HAS TO COME FROM THE OWNERS, FROM THE PEOPLE RUNNING THE PROCESS

I. T. NEEDS TO ENABLE TOOLS, AND ENABLE CONNECTING TO THE DATABASES, TO THE SQL SERVERS. S. A. P. CABLES, BUT AT THE END OF THE DAY IT CAN’T BE PUSHED INTO USERS. THIS IS SOMETHING THAT IT’S RELATIVELY AD HOC. CANNOT BE TOPS DOWN, IT NEEDS TO START WITH PEOPLE RUNNING THE PROCESS IT’S REALLY GOOD TO HAVE CHAMPION OR ADVOCATES. IN THE TEAM, I WAS HELPING PEOPLE OUT. IF THEY HAD QUESTIONS ABOUT IT. SOME ONE WHO REALLY KNEW ABOUT POWER PIVOT AND POSTED A SERIES OF ROTATING SESSIONS EACH WEEK WE’RE GOING TO SIT AN HOUR AND A HALF AND TALK ABOUT DIFFERENT TOPICS ABOUT DATA ANALYSIS. ONE DAY POWER PRAIRIE, ONE DAY POWER PIVOT, IT CREATED THAT MOMENTUM IN THE ORGANIZE TO LEVERAGE IT IT IS FASCINATING, I LEFT THE ORGANIZE OVER A YEAR AGO, AND IN PUTTING THIS CONTENT TOGETHER, I REACHED OUT TO PEOPLE I WAS WORKING WITH, JUST TO MAKE SURE IT MADE SENSE AND GET IDEAS HOW TO PRESENT IT IF THEY’RE DOING STUFF I NEVER THOUGHT AT THAT POINT THAT IS REALLY COOL TO SEE. THAT LID ON IN THE BUSINESS USER. WITH THIS I END THE SESSION I HAVE SEVERAL RESOURCES HERE IN THE FORM OF — THIS CONTENT WILL BE PUBLISHED IN THE PORTAL, IN THE CONFERENCE SITE, MAKE SURE TO CHECK IT OUT TO HAVE ALL OF THE DIRECT LIMPBS. BUT THIS IS GREAT, THESE ARE GREAT RESOURCES, SO YOU HAVE DOCUMENTATION FOR FLOW, FOR POWER B. I. , AND RESOURCES ON SPELL JOIN OUR TECH COMMUNITY WHICH HAS GREAT RESOURCES. CHECK THE EXCEL SESSIONS. WE HAVE ONE THIS AFTERNOON ON ADVANCED MODELLING USING DYNAMIC ARRAYS. AND WE HAVE SEVERAL OTHERS THAT MANY WERE YESTERDAY OR THIS MORNING, MAKE SURE TO CHECK THE RECORDINGS AS WELL IF YOU’RE INTERESTED I’LL STICK AROUND IF YOU HAVE ANY QUESTIONS OR JUST WANT TO TALK ABOUT ANY OF YOUR EXPERIENCES. THANKS