cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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.

 

 

image.png

 

[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

2 REPLIES 2
Solution Specialist
Solution Specialist

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
Not applicable

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"

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors