{"text_format":"3","workbook":"xlsx","n_sheets":19,"sheets":["Start","1","2","3","4","5","6","7","8","9","10","11","12","13","14","Import data from web","Categories","Sales Data","tbl_Sheet5data"],"not_rendered":{"images":18,"defined_names":30},"read":"whole","date_system":"1900"}

{"sheet":"Start","index":0,"n_rows":4,"n_cols":1,"rows_kept":4}
["Welcome\nInstructions for screen readers: After you go through the tip sheets in this tutorial, you'll learn how to quickly master chart elements. The instructions for each sheet start in cell A1, and each subsequent step is in cell A2, A3, and so on. \nThe instructions will indicate which cells to navigate to for use of a feature, or further reading.\nTo start, press CTRL+PAGE DOWN."]
["Get to know Power Query"]
["Need to import or paste data into Excel? Would you also\nlike to clean it up, and keep it up-to-date? Power Query \nis the answer, and this tutorial will show you the ropes.\n"]
["Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN."]

{"sheet":"1","index":1,"n_rows":15,"n_cols":4,"rows_kept":15}
["Does this happen to you? You paste data into Excel, but then you have to clean it up. For example, in the data below, there are a couple things you might want to do..."]
["First, you might want to split this column into two columns: One for first name, and one for last name..."]
["You might also want to remove \"12:00 AM\" from the last column, since it's repetitive and unnecessary."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]
[]
[]
[]
[]
[]
[]
[null,"Product","Sales rep","Order date"]
[null,"Beer","Anne Cho","2018-01-15"]
[null,"Plums","Tim Anders","2018-01-15"]
[null,"Pears","Jan Kotas","2018-01-20"]
[null,"Apples","Sam Ioto","2018-01-20"]

{"sheet":"2","index":2,"n_rows":15,"n_cols":13,"rows_kept":15}
["You could clean up the data manually, but this can be time consuming..."]
["You could fix this by adding a new column for the last names, and then you could cut and paste the last names into that new column…"]
["...You could also reformat the last column to exclude \u000b\"12:00 AM,\" but that's a lot of work…"]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]
[]
[]
[]
[]
[]
[{"$nulls":12}," "]
[null,"Product","Sales rep","Order date"]
[null,"Beer","Anne Cho","2018-01-15"]
[null,"Plums","Tim Anders","2018-01-15"]
[null,"Pears","Jan Kotas","2018-01-20"]
[null,"Apples","Sam Ioto","2018-01-20"]

{"sheet":"3","index":3,"n_rows":2,"n_cols":1,"rows_kept":2}
["Forget manually cutting, pasting, and reformatting. A better way to clean up data is to use Power Query."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"4","index":4,"n_rows":15,"n_cols":11,"rows_kept":15}
["What is Power Query? It's a special editor that allows you to clean data so that it's easier to work with. "]
["The original data can be found in cells B11 through D15."]
["Then we used Power Query to create the cleaned up data table found in cells H11 through K15."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]
[]
[]
[]
[]
[]
[]
[null,"Product","Sales rep","Order date",{"$nulls":3},"Product","First name","Last name","Order date"]
[null,"Beer","Anne Cho","2018-01-15",{"$nulls":3},"Beer","Anne","Cho","2018-01-15"]
[null,"Plums","Tim Anders","2018-01-15",{"$nulls":3},"Plums","Tim","Anders","2018-01-15"]
[null,"Pears","Jan Kotas","2018-01-20",{"$nulls":3},"Pears","Jan","Kotas","2018-01-20"]
[null,"Apples","Sam Ioto","2018-01-20",{"$nulls":3},"Apples","Sam","Ioto","2018-01-20"]

{"sheet":"5","index":5,"n_rows":12,"n_cols":10,"rows_kept":12}
["Another great thing about Power Query: If you add new data that's \"dirty,\" Power Query can clean that up too, with just a click of a button. Try it out below!"]
["Cells B7 through D12 contain data. There are three columns: Product, Sales rep, and Order date. Each column has various values for each row."]
["Cells G7 through J12 contain data. There are three columns: Product, First name, Last name, and Order date. Each column has various values for each row."]
["TRY THIS: If you see the Enable Content button at the top of Excel, select it (ALT+F, N). Then type your first and last name into cell C12."]
["...Then on the Data tab, click Refresh All (ALT+A, R). You'll see that Power Query added the new row for Kiwis, and it cleaned up the data by splitting your first name into cell H12 and your last name into cell I12."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]
[null,"Product","Sales rep","Order date",null,null,"Product","First Name","Last Name","Order date"]
[null,"Bananas","Anne Cho","2018-01-15",null,null,"Bananas","Anne","Cho","2018-01-15"]
[null,"Plums","Tim Anders","2018-01-15",null,null,"Plums","Tim","Anders","2018-01-15"]
[null,"Pears","Jan Kotas","2018-01-20",null,null,"Pears","Jan","Kotas","2018-01-20"]
[null,"Apples","Sam Ioto","2018-01-20",null,null,"Apples","Sam","Ioto","2018-01-20"]
[null,"Kiwis",null,{"$f":"TODAY()","v":"2021-09-16"},null,null,"Kiwis",null,null,"2021-09-16"]

{"sheet":"6","index":6,"n_rows":11,"n_cols":10,"rows_kept":11}
["Why do they call it Power Query? Between the original data, and the clean data, there's a powerful query that links the two. "]
["Cells B7 through D11 contain data. There are three columns: Product, Sales rep, and Order date. Each column has various values for each row."]
["Cells G7 through J11 contain data. There are four columns: Product, First name, Last name, and Order date. Each column has various values for each row."]
["IMPORTANT DETAIL: Between the original data and the clean data, there's a query. The query contains all of the cleaning steps, so you don't have to redo them again if new data is added. All you have to do is select Refresh (Alt+A, R), and Power Query cleans it up automatically."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]
[]
[null,"Product","Sales rep","Order date",null,null,"Product","First name","Last name","Order date"]
[null,"Bananas","Anne Cho","2018-01-15",null,null,"Bananas","Anne","Cho","2018-01-15"]
[null,"Plums","Tim Anders","2018-01-15",null,null,"Plums","Tim","Anders","2018-01-15"]
[null,"Pears","Jan Kotas","2018-01-20",null,null,"Pears","Jan","Kotas","2018-01-20"]
[null,"Apples","Sam Ioto","2018-01-20",null,null,"Apples","Sam","Ioto","2018-01-20"]

{"sheet":"7","index":7,"n_rows":2,"n_cols":1,"rows_kept":2}
["There are three main steps to using Power Query: Get Data, Transform, and Load. "]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"8","index":8,"n_rows":2,"n_cols":1,"rows_kept":2}
["The first step is Get Data. This is simply the process of getting your data into Power Query. We'll show you how this works in the videos coming up. "]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"9","index":9,"n_rows":3,"n_cols":1,"rows_kept":3}
[]
["EXCEL SPEAK: Why the word, \"Transform?\" Well, you can do more than just clean the data. You can add or remove columns, filter, add custom calculations, and a lot more. "]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"10","index":10,"n_rows":2,"n_cols":1,"rows_kept":2}
["And finally, Loading is the last step. This is the act of returning the cleaned-up, transformed data back into Excel. "]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"11","index":11,"n_rows":14,"n_cols":9,"rows_kept":14}
["And keep in mind: Power Query isn't just for cleaning up pasted data. You can import and transform data from other sources, like a file, a database, or even a web page."]
["Cells F9 through I14 contain data. There are four columns: Year, Champions, Runners-up, Third place. Each column has various values for each row."]
["CHECK THIS OUT: In this example we used Power Query to get data from Wikipedia's FIFA World Cup page. You can imagine other useful sources you might pull data from. "]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]
[]
[]
[]
[]
[{"$nulls":5},"Year","Champions","Runners-up","Third place"]
[{"$nulls":5},"2002 \r\n","Brazil","Germany","Turkey"]
[{"$nulls":5},"2006 \r\n","Italy","France","Germany"]
[{"$nulls":5},"2010 \r\n","Spain","Netherlands","Germany"]
[{"$nulls":5},"2014 \r\n","Germany","Argentina","Netherlands"]
[{"$nulls":5},"2018 \r\n","France","Croatia","Belgium"]

{"sheet":"12","index":12,"n_rows":3,"n_cols":1,"rows_kept":3}
["Before we show you how to use Power Query, we should warn you that its window, called the Power Query Editor, is large and may cover Excel. This is expected. "]
["GOOD TO KNOW: You can't use the Excel window while the Power Query Editor is open. However, once you're done transforming the data, you can close the editor. At that point, you'll able to work in the Excel window again."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"13","index":13,"n_rows":5,"n_cols":1,"rows_kept":5}
["In fact, if you have two monitors, we recommend moving the Power Query Editor window to the second monitor so that you can view Excel at the same time for reference. "]
["Two monitors side by side with Excel opened on one and Power Query editor on the other."]
["If you have two monitors, put the Excel window in one…"]
["... and put the Power Query Editor \u000bin the other. "]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"14","index":14,"n_rows":6,"n_cols":1,"rows_kept":6}
["Start the videos"]
["1. Take a look at the sheets at the bottom of this workbook. You'll see sheets for Import data from web, Categories, and Sales Data ... You'll work with these sheets while you watch the videos. "]
["2. Where are the videos? They'll appear in a web browser after you click the link in step 4. "]
["3. Keep in mind, there are several videos, so make sure to bookmark the page. That way you can return to them in case you need to take a break. "]
["4. Watch the Power Query videos."]
["To continue with this tutorial, press CTRL + PAGE DOWN. To go to the previous sheet, press CTRL + PAGE UP."]

{"sheet":"Import data from web","index":15,"n_rows":2,"n_cols":1,"rows_kept":2}
["Copy this URL:"]
["https://en.wikipedia.org/wiki/FIFA_World_Cup"]

{"sheet":"Categories","index":16,"n_rows":7,"n_cols":2,"rows_kept":7}
["Product Name","Category"]
["Apples","Produce"]
["Beer","Beverages"]
["Coffee","Beverages"]
["Green Tea","Beverages"]
["Pears","Produce"]
["Plums","Produce"]

{"sheet":"Sales Data","index":17,"n_rows":25,"n_cols":4,"rows_kept":25}
["txt_ProdNam","txt_SalesRep","dt_OrderDate","dbl_Sales"]
["Beer","Anne Hellung-Larsen","2018-01-15",{"v":14000.36,"d":"14,000.36"}]
["Plums","Anne Hellung-Larsen","2018-01-15",{"v":1050.42,"d":"1,050.42"}]
["Total Sales Rep",null,null,{"$f":"SUM(D2:D3)","v":{"v":15050.78,"d":"15,050.78"}}]
["Pears","Jan Kotas","2018-01-20",{"v":3000.51,"d":"3,000.51"}]
["Apples","Jan Kotas","2018-02-20",{"v":5300.04,"d":"5,300.04"}]
["Plums","Jan Kotas","2018-02-20",350.13]
["Total Sales Rep",null,null,{"$f":"SUM(D5:D7)","v":{"v":8650.679999999998,"d":"8,650.68"}}]
["Coffee","Mariya Sergienko","2018-02-27",{"v":9200.97,"d":"9,200.97"}]
["Total Sales Rep",null,null,{"$f":"D9","v":{"v":9200.97,"d":"9,200.97"}}]
["Green Tea","Anne Hellung-Larsen","2018-03-10",{"v":13800.89,"d":"13,800.89"}]
["Total Sales Rep",null,null,{"$f":"D11","v":{"v":13800.89,"d":"13,800.89"}}]
["Coffee","Mariya Sergienko","2018-03-24",{"v":5980.4,"d":"5,980.40"}]
["Total Sales Rep",null,null,{"$f":"D13","v":{"v":5980.4,"d":"5,980.40"}}]
["Beer","Nancy Freehafer","2018-04-05",{"v":12180.68,"d":"12,180.68"}]
["Total Sales Rep",null,null,{"$f":"D15","v":{"v":12180.68,"d":"12,180.68"}}]
["Beer","Michael Neipper","2018-04-08",{"v":42000.42,"d":"42,000.42"}]
["Total Sales Rep",null,null,{"$f":"D17","v":{"v":42000.42,"d":"42,000.42"}}]
["Apples","Mariya Sergienko","2018-05-24",{"v":2000.39,"d":"2,000.39"}]
["Plums","Nancy Freehafer","2018-05-24",{"v":52.5,"d":"52.50"}]
["Coffee","Nancy Freehafer","2018-06-07",{"v":2300.2,"d":"2,300.20"}]
["Total Sales Rep",null,null,{"$f":"SUM(D19:D21)","v":{"v":4353.09,"d":"4,353.09"}}]
["Apples","Andrew Cencini","2018-06-23",{"v":15900.61,"d":"15,900.61"}]
["Pears","Andrew Cencini","2018-06-23",{"v":9000.24,"d":"9,000.24"}]
["Total Sales Rep",null,null,{"$f":"SUM(D23:D24)","v":{"v":24900.85,"d":"24,900.85"}}]

{"sheet":"tbl_Sheet5data","index":18,"n_rows":6,"n_cols":4,"rows_kept":6,"hidden":true}
["Product","First Name","Last Name","Order date"]
["Bananas","Anne","Cho","2018-01-15"]
["Plums","Tim","Anders","2018-01-15"]
["Pears","Jan","Kotas","2018-01-20"]
["Apples","Sam","Ioto","2018-01-20"]
["Kiwis",null,null,"2021-09-16"]