Add a Column to Excel source data

[Centaur]

Hello, 

I have a folder with many files (100's).

I am consolidating these files with PQ (the data in each file is a table format)

Same #columns and column field names in all the files. 

I now need to add a field (column) to my source data in excel.

I added this field (column) to only 1 of the source data files (the most recent one)

After I refresh PQ, I do not see this field in the PQ data set. 

Do I need to add this field (column) to all the source data files instead of only 1 of files?  (I have hundreds of files and want to avoid having to add to all the files)

here is a few lines of the advanced editor (I have many and assume only the first few lines might show where my issue is)

I am a novice user of PQ. 

thank you for the help. 

let
Source = Folder.Files("C:\Users\Me\Box\Financing\Project Finance\DRAWS\ConsolidatePQ"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Table")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Custom.Name", "Custom.Data"}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Vendor", "Invoice #", "Invoice amount", "Currency", "USD Amount", "Invoice date", "Invoice due date", "Budget Category", "Account"}, {"Custom.Data.Vendor", "Custom.Data.Invoice #", "Custom.Data.Invoice amount", "Custom.Data.Currency", "Custom.Data.USD Amount", "Custom.Data.Invoice date", "Custom.Data.Invoice due date", "Custom.Data.Budget Category", "Custom.Data.Account"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom.Data","(x)","",Replacer.ReplaceText,{"Custom.Data.Vendor"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," - Conditional Lien Waiver","",Replacer.ReplaceText,{"Custom.Data.Vendor"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Custom.Data.Invoice amount", "Invoice amount"}, {"Custom.Data.Invoice #", "Invoice #"}, {"Custom.Data.Vendor", "Vendor"}, {"Custom.Name", "Named Range"}, {"Name", "File Name"}, {"Custom.Data.Currency", "Currency"}, {"Custom.Data.USD Amount", "USD Amount"}, {"Custom.Data.Invoice date", "Invoice date"}, {"Custom.Data.Invoice due date", "Invoice due date"}, {"Custom.Data.Budget Category", "Budget Category"}, {"Custom.Data.Account", "Account"}}),

[Centaur]

Hi daniel,

sorry but I am not following.  In the entire code above, i did replace my code with your code (bolded).  Could you provide a little more detail?  Thank you very much. 

[wdx223_Daniel]

try to use my code to create a new query

[wdx223_Daniel]

let
Source = Folder.Files("C:\Users\Me\Box\Financing\Project Finance\DRAWS\ConsolidatePQ"),

Custom1=Table.FromPartitions("FileName",Table.ToList(Source[[Name],[Content]],each {_{0},Table.FormPartitions("Name",Table.ToList(Table.SelectRows(Exce.Workbook(_{1}),each [Kind]="Table")[[Name],[Data]]))})),

Custom2=Table.TransformColumns(Custom1,{"Vendor",each Text.Combine(Splitter.SplitTextByAnyDelimiter({"(x)"," - Conditional Lien Waiver"})(_))})

in

Custom2

[Centaur]

hello Daniel.  Thank you.  I am not sure if I did it right but I am getting an error.   

The below is the entire Advanced Editor with your proposed changes above.  

if could kindly let me know where I am wrong would appreciate greatly. 

let
Source = Folder.Files("C:\Users\ME\Box\Financing\Project Finance\DRAWS\ConsolidatePQ"),
Custom1=Table.FromPartitions("FileName",Table.ToList(Source[[Name],[Content]],each {_{0},Table.FormPartitions("Name",Table.ToList(Table.SelectRows(Exce.Workbook(_{1}),each [Kind]="Table")[[Name],[Data]]))})),
Custom2=Table.TransformColumns(Custom1,{"Vendor",each Text.Combine(Splitter.SplitTextByAnyDelimiter({"(x)"," - Conditional Lien Waiver"})(_))})
in
Custom2,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Table")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Name", "Custom.Name", "Custom.Data"}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Vendor", "Invoice #", "Invoice amount", "Currency", "USD Amount", "Invoice date", "Invoice due date", "Budget Category", "Account"}, {"Custom.Data.Vendor", "Custom.Data.Invoice #", "Custom.Data.Invoice amount", "Custom.Data.Currency", "Custom.Data.USD Amount", "Custom.Data.Invoice date", "Custom.Data.Invoice due date", "Custom.Data.Budget Category", "Custom.Data.Account"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom.Data","(x)","",Replacer.ReplaceText,{"Custom.Data.Vendor"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," - Conditional Lien Waiver","",Replacer.ReplaceText,{"Custom.Data.Vendor"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Custom.Data.Invoice amount", "Invoice amount"}, {"Custom.Data.Invoice #", "Invoice #"}, {"Custom.Data.Vendor", "Vendor"}, {"Custom.Name", "Named Range"}, {"Name", "File Name"}, {"Custom.Data.Currency", "Currency"}, {"Custom.Data.USD Amount", "USD Amount"}, {"Custom.Data.Invoice date", "Invoice date"}, {"Custom.Data.Invoice due date", "Invoice due date"}, {"Custom.Data.Budget Category", "Budget Category"}, {"Custom.Data.Account", "Account"}}),
#"Split Column by Position" = Table.SplitColumn(#"Renamed Columns", "Named Range", Splitter.SplitTextByRepeatedLengths(4), {"Named Range.1", "Named Range.2"}),
#"Replaced Value6" = Table.ReplaceValue(#"Split Column by Position","D","DD",Replacer.ReplaceText,{"Named Range.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value6",{{"Named Range.1", type text}, {"Named Range.2", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Named Range.2", "DDNo"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Named Range.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Vendor] <> "Total")),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"DDNo", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "File Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"File Name.1", "File Name.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"File Name.1", type text}, {"File Name.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "File Name.2", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"File Name.2.1", "File Name.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"File Name.2.1", type date}, {"File Name.2.2", type text}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type3",#date(2021, 12, 15),#date(2021, 12, 14),Replacer.ReplaceValue,{"File Name.2.1"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",#date(2021, 9, 8),#date(2021, 9, 15),Replacer.ReplaceValue,{"File Name.2.1"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value3",{{"File Name.2.1", type date}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type4",{{"File Name.2.1", "Funding Date"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"File Name.2.2", "File Name.1"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Removed Columns1",{{"USD Amount", Currency.Type}}),
NewStep=Table.FromRecords(Table.TransformRows(#"Changed Type5",each if [Invoice date] is text then _&[Vendor=[Invoice date],Invoice date=null] else _)),
#"Changed Type6" = Table.TransformColumnTypes(NewStep,{{"Funding Date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type6", "Curr New", each if [Currency] = "Total" or [Currency] = "LA" or [Currency] = "DA" then "" else [Currency]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Vendor New", each if [Currency] = "Total" then "Total" else [Vendor]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Account New", each if [Currency] = "DA" then "DA" else [Account]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Account New.1", each if [Currency] = "LA" then "LA" else [Account]),
#"Merged Columns" = Table.CombineColumns(#"Added Custom4",{"Account New", "Account New.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Replaced Value4" = Table.ReplaceValue(#"Merged Columns","LALA","LA",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","DADA","DA",Replacer.ReplaceText,{"Merged"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value5",{"Funding Date", "DDNo", "Vendor", "Invoice #", "Invoice amount", "Currency", "USD Amount", "Invoice date", "Invoice due date", "Budget Category", "Account", "Merged", "Curr New", "Vendor New"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Account"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Merged", "Account"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns3",{"Funding Date", "DDNo", "Vendor", "Invoice #", "Invoice amount", "Currency", "Curr New", "USD Amount", "Invoice date", "Invoice due date", "Budget Category", "Account", "Vendor New"}),
#"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns1",{"Currency"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns3",{{"Curr New", "Currency"}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns4",{"Funding Date", "DDNo", "Vendor", "Vendor New", "Invoice #", "Invoice amount", "Currency", "USD Amount", "Invoice date", "Invoice due date", "Budget Category", "Account"}),
#"Removed Columns4" = Table.RemoveColumns(#"Reordered Columns2",{"Vendor"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns4",{{"Vendor New", "Vendor"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns5",{{"Invoice due date", type date}, {"Invoice date", type date}, {"USD Amount", Currency.Type}, {"Invoice amount", Currency.Type}, {"Vendor", type text}, {"DDNo", type text}, {"Budget Category", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type7",{{"Vendor", Text.Trim, type text}}),
#"Changed Type8" = Table.TransformColumnTypes(#"Trimmed Text",{{"Invoice #", type text}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type8", "Invoice No Stripped", each [#"Invoice #"]),
#"Reordered Columns3" = Table.ReorderColumns(#"Added Custom5",{"Funding Date", "DDNo", "Vendor", "Invoice #", "Invoice No Stripped", "Invoice amount", "Currency", "USD Amount", "Invoice date", "Invoice due date", "Budget Category", "Account"}),
#"Replaced Value7" = Table.ReplaceValue(#"Reordered Columns3","-","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","/","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","\","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9",".","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","(","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11",")","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","#","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13",",","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14"," ","",Replacer.ReplaceText,{"Invoice No Stripped"}),
#"Trimmed Text1" = Table.TransformColumns(#"Replaced Value15",{{"Invoice No Stripped", each Text.TrimStart( _, "0"), type text}}),
#"Replaced Value16" = Table.ReplaceValue(#"Trimmed Text1"," (x)","",Replacer.ReplaceText,{"Vendor"}),
#"Trimmed Text2" = Table.TransformColumns(#"Replaced Value16",{{"Vendor", Text.Trim, type text}}),
#"Replaced Value17" = Table.ReplaceValue(#"Trimmed Text2","Marpro Marine","Marpro Marine, LLC",Replacer.ReplaceText,{"Vendor"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","Marpro Marine, LLC, LLC","Marpro Marine, LLC",Replacer.ReplaceText,{"Vendor"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","Bladt - Hedge Settlement","Bladt Industries",Replacer.ReplaceText,{"Vendor"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","Bladt Industries - Hedge Settlement","Bladt Industries",Replacer.ReplaceText,{"Vendor"}),
#"Filtered Rows2" = Table.SelectRows(#"Replaced Value20", each true)
in
#"Filtered Rows2"

[wdx223_Daniel · Accepted solution]

use my code replace all your code

[Centaur]

thank you.  I was able to modify the current code.  It is tricky to do this.   I had to modify the 4th line of code but then had to change names for the following lines of code since they referenced names that no longer existed.  Not straight forward but I managed to do it.  thank you for the help.