none
Query to pull data from multiple worksheets in the same workbook, only want rows 13 to 37 in each sheet. RRS feed

  • Question

  • I've got a workbook that contains financial information for my company.  Each business unit has it's own worksheet (Ex: branch 200 has a worksheet named "200". There is a ton of header information in each worksheet, and also a bunch of data below the range I want to extract that I don't need.  I really just need rows 13 through 37 in each worksheet.  I also need to add a column that has the worksheet name so I can identify which business unit the rows of data is for.

    Below is the information from the advanced editor in a query for just branch 200.  I thought about just creating blank queries for each, and changing the ([Name] = "200") for the next business unit, etc. etc. (70 business units = 70 queries) and then append them all together into one query... would that cause my laptop to melt?  Thanks in advance for the help!

    let
        Source = Excel.Workbook(File.Contents("C:\Users\swagner\Desktop\Ops Dashboard Data Files\Dashboard - Powerpivot\Intermediate Queries\05 2016 Stack SUMMARY Mayer Fin.xls"), null, true),
        #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Print_Area")),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.CurrentWorkbook()),
        #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Name] = "200")),
        #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66"}),
        #"Removed Top Rows" = Table.Skip(#"Expanded Data",12),
        #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",25),
        #"Filtered Rows2" = Table.SelectRows(#"Kept First Rows", each ([Column1] <> null)),
        #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Column1", "Date"}}),
        #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","-"," 20",Replacer.ReplaceText,{"Date"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}, {"Name", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column4", "Column7", "Column10", "Column12", "Column15", "Column17", "Column20", "Column25", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Custom"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Column2", type number}, {"Column3", type number}, {"Column5", type number}, {"Column6", type number}, {"Column8", type number}, {"Column9", type number}, {"Column11", type number}, {"Column13", type number}, {"Column14", type number}, {"Column16", type number}, {"Column18", type number}, {"Column19", type number}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column26", type number}, {"Column27", type number}}),
        #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Column2", "Stock Sales"}, {"Column3", "Stock GM"}, {"Column5", "Non-Stock Sales"}, {"Column6", "Non-Stock GM"}, {"Column8", "Direct Sales"}, {"Column9", "Direct GM"}, {"Column11", "Net Disc & Frt"}, {"Column13", "Total Sales"}, {"Column14", "Total GM"}, {"Column16", "Total P/R"}, {"Column18", "I-C Alloc"}, {"Column19", "Total Op Exp"}, {"Column21", "Oper Inc"}, {"Column22", "Rebate"}, {"Column23", "Other Exp"}, {"Column24", "Inc B/F I & T"}, {"Column26", "Int Exp"}, {"Column27", "Inc B/F Taxes"}}),
        #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Branch", each [Name]&"500"),
        #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Name", "Branch", "Date", "Stock Sales", "Stock GM", "Non-Stock Sales", "Non-Stock GM", "Direct Sales", "Direct GM", "Net Disc & Frt", "Total Sales", "Total GM", "Total P/R", "I-C Alloc", "Total Op Exp", "Oper Inc", "Rebate", "Other Exp", "Inc B/F I & T", "Int Exp", "Inc B/F Taxes"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Name"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Branch", type text}})
    in
        #"Changed Type2"

    Friday, July 1, 2016 12:49 PM

Answers