Add Column from specific cell, then pull rows starting at row 7

[Anonymous]

I need help creating a query that will add columns based on the content on 4 cells. (D1, D2, D3, E4), then add to the content starting at A6:BA6 (Headers). Extracting the headers and rows below Row6 is straight forward. However, I have never added a column based on a single cell reference to all rows. That said -- I need to add 4 columns based on (D1, D2, D3, E4) and add them to all rows to right of other data. I will combining over 1000+ (CSV files). The CSV Files were originally XLS workbooks. Is there an example of this someone can point me to? I have imported workbooks, removing top/bottow rows etc. but need help with the step(s) needed to add the four columns based from the 4 cells (which should always be in same place) to every row of the file before merging/appending/combining. Hopefully this makes sense. See screenshot below for sample data.

[Edit] It would be helpful if the query would ignore any files that did not have data in those cells (D1, D2, D3, E4) -- or (C1:C3) LABEL/Customer/Last Updated.

Thanks, Kevin

[Anonymous]

The code below would handle one sheet. You would need to adapt it to work with the folder. 

Regards,

Mike

let
    Source = Excel.Workbook(File.Contents("C:\t\Orders.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    AddLabel = Table.AddColumn(Sheet2_Sheet, "Label", each if [Column3] = "Label:" then [Column4] else null, type text),
    AddCustomer = Table.AddColumn(AddLabel, "Customer", each   if [Column3] = "Customer:" then [Column4] else null, type text),
    AddLastUpdated = Table.AddColumn(AddCustomer, "LastUpdated", each if [Column3] = "Last Updated:" then DateTime.From(Text.Replace([Column4], ",","")) else null, type datetime),
    #"Filled Down" = Table.FillDown(AddLastUpdated,{"Label", "Customer", "LastUpdated"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    RenameCalcCols = Table.RenameColumns(#"Promoted Headers", List.Zip({List.LastN(Table.ColumnNames(#"Promoted Headers"),3), {"Label","Customer","LastUpdated"}}))
in
    RenameCalcCols

[Anonymous]

Thank Mike - that was helpful!

Below is what I have now -- but I think I need to fix something to get it to work. Not sure if I need a parameter / depend query. I guess I am missing something

let
    Source = Folder.Files("P:\History - Margin Sheet - CSVs"),
    AddLabel = Table.AddColumn(Sheet2_Sheet, "Label", each if [Column3] = "Label:" then [Column4] else null, type text),
    AddCustomer = Table.AddColumn(AddLabel, "Customer", each   if [Column3] = "Customer:" then [Column4] else null, type text),
    AddLastUpdated = Table.AddColumn(AddCustomer, "LastUpdated", each if [Column3] = "Last Updated:" then DateTime.From(Text.Replace([Column4], ",","")) else null, type datetime),
    #"Filled Down" = Table.FillDown(AddLastUpdated,{"Label", "Customer", "LastUpdated"}),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fall 2016 - Walmart - College - Mens - Revised - 10-26-15.csv", type text}, {"Line", Int64.Type}, {"STYLE", type text}, {"DEV STYLE", type text}, {"STYLE DESCRIPTION", type text}, {"Standard Cost/LDP", type text}, {"TY Wholesale Price", Currency.Type}, {"LY Actual Wholesale Price", Currency.Type}, {"Wholesale %Change", Percentage.Type}, {"TY Forecasted Quantity", Int64.Type}, {"LY Actual Quantity", Int64.Type}, {"TY Gross Sales $", Currency.Type}, {"SCOS $", Currency.Type}, {"Forecast Allowance %", Percentage.Type}, {"Forecast Allowance $", Currency.Type}, {"Net Sales $", Currency.Type}, {"Standard Margin $", Currency.Type}, {"Standard Margin %", Percentage.Type}, {"Royalty Rate %", Percentage.Type}, {"Forecast Royalty $", Currency.Type}, {"Gross Margin $", Currency.Type}, {"Gross Margin %", Percentage.Type}, {"TY Retail Price", Currency.Type}, {"Retail Margin %", Percentage.Type}, {"Standard Cost", Currency.Type}, {"Factory Cost (FOB)", Currency.Type}, {"LY FOB Price", Currency.Type}, {"FOB % Change", Percentage.Type}, {"Comm. $", Currency.Type}, {"Comm. % Rate", Percentage.Type}, {"Pallet Costs", Currency.Type}, {"Standard Freight Rate", Currency.Type}, {"Duty & Brokerage $", Currency.Type}, {"Duty %", Percentage.Type}, {"Brokerage %", Percentage.Type}, {"TY Factory", type text}, {"Retail $", Currency.Type}, {"Comp Style", type text}, {"Prog. Royalty", Int64.Type}, {"Req. FOB", type text}, {"Req. Margin", Percentage.Type}, {"Req. Wholesale", type text}, {"Duty Free?", Int64.Type}, {"Fabric Number", type text}, {"Fabric Content", type text}, {"Fabric Description", type text}, {"Fabric Number 2", type text}, {"Fabric Content 2", type text}, {"Fabric Description 2", type text}, {"Fabric Number 3", type text}, {"Fabric Content 3", type text}, {"Fabric Description 3", type text}, {"Color", type text}, {"Logo", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"STYLE", "STYLE DESCRIPTION", "Standard Cost/LDP", "TY Wholesale Price", "TY Forecasted Quantity", "Forecast Allowance %", "Royalty Rate %", "TY Retail Price", "Standard Cost", "Factory Cost (FOB)", "Comm. % Rate", "Pallet Costs", "Standard Freight Rate", "Duty %", "Brokerage %", "TY Factory", "Duty Free?", "Fabric Number", "Fabric Content", "Fabric Description", "Fabric Number 2", "Fabric Content 2", "Fabric Description 2", "Fabric Number 3", "Fabric Content 3", "Fabric Description 3", "Color", "Logo"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([STYLE] <> "") and ([STYLE DESCRIPTION] <> "")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Standard Cost/LDP", Currency.Type}, {"Duty Free?", type logical}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type2"),
    #"Inserted First Characters" = Table.AddColumn(#"Removed Errors", "First Characters", each Text.Start([STYLE], 2), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "Brand"}}),
    #"Inserted Text Range" = Table.AddColumn(#"Renamed Columns", "Text Range", each Text.Middle([STYLE], 2, 1), type text),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Text Range",{{"Text Range", "Gender Code"}}),
    #"Inserted Text Range1" = Table.AddColumn(#"Renamed Columns2", "Text Range", each Text.Middle([STYLE], 3, 4), type text),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Text Range1",{{"Text Range", "Short Style"}}),
    #"Inserted Text Range2" = Table.AddColumn(#"Renamed Columns3", "Text Range", each Text.Middle([STYLE], 7, 3), type text),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted Text Range2",{{"Text Range", "Cust Code"}}),
    #"Inserted Literal" = Table.AddColumn(#"Renamed Columns4", "Literal", each "SHORT SLEEVE CREW NECK FRUIT TEE", type text),
    #"Renamed Columns5" = Table.RenameColumns(#"Inserted Literal",{{"Literal", "Short Desc"}})
in
    #"Renamed Columns5"