Basic Excel Business Analytics #31: Power Query: Import Multiple Excel Files with 1 Sheet Each

Опубликовано: 19 Октябрь 2015
на канале: excelisfun

14,678

132

Download files: https://people.highline.edu/mgirvin/A...
Learn about how to import multiple Excel workbooks into a single table and make a PivotTable that groups Sales Numbers and a Histogram
1) (00:04) Into to files needed for video
2) (00:24) Look at Excel files we need to import.
3) (01:06) Power Query to Import Excel files from Folder, including adding a new Column with the Power Query Function Excel.Workbook that will allow us to import the data from each Excel Workbook.
4) (01:55) Excel.Workbook Power Query Function
5) (03:10) Filter “Does Not Contain” “Sheet” so that sheets not named are filtered out.
6) (05:08) Load to Table so we can group in a PivotTable. We are not loading large data to Data Model because Data Model will not allow Grouping in a PivotTable.
7) (05:56) Edit Query when you make a mistake
8) (07:00) Create PivotTable that groups Sales Numbers and a Histogram
9) (07:26) Group with an max limit that is smaller than the max value in the data set to force the PivotTable to create a “catch all” upper category to gather up the last few categories that contain only a few frequencies in each.
10) (11:03) Look at large file size for big data import, but we decide to accept it because we want the benefit of grouping in a PivotTable
11) (11:41) Summary and Conclusion

Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.