Split data source/query table into tables/querys

Hey Karla,

Your two queries should be something along these lines.

First Table:

let
    Source = Excel.Workbook(File.Contents("---insert file path---"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Active Projects - 48", type text}, {"Computer Users", Int64.Type}, {"Project IT Staff", Int64.Type}, {"""AS Sold"" Project Cost", Int64.Type}, {"Project IT Budgets Current IT Spend of all Projects", Int64.Type}, {"Current IT Spend of all Projects", Int64.Type}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type", each [#"Active Projects - 48"] <> null),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Active Projects - 48", "Computer Users", "Project IT Staff", """AS Sold"" Project Cost", "Project IT Budgets Current IT Spend of all Projects", "Current IT Spend of all Projects"})
in
    #"Removed Other Columns"

Second Table:

let
    Source = Excel.Workbook(File.Contents("---insert file path---"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Kept Last Rows" = Table.LastN(Sheet1_Sheet, each [Column1] <> null),
    #"Promoted Headers" = Table.PromoteHeaders(#"Kept Last Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Mega Projects", type text}, {"Forecast Completion", type date}, {"Project % Complete", Int64.Type}, {"Computer Users", Int64.Type}, {"Craft", Int64.Type}, {"Project IT Staff", Int64.Type}, {"""AS Sold"" Project Cost", type text}, {"Project IT Budgets", Int64.Type}, {"% of ""As Sold"" Contract", Int64.Type}, {"Current IT Spend", type text}})
in
    #"Changed Type"

Please note that this is not the cleanest way to pull in data and you will have to edit the query if columns change.

Kind regards,

Alex