Forum Discussion
Add a Column to Excel source data
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"}}),
use my code replace all your code
6 Replies
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
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"use my code replace all your code
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.
try to use my code to create a new query
