Forum Discussion
Dynamically create column if a particular column does not exist within the data
- 8 years ago
Hi,
This code works fine for me. I saved all 3 files on a desktop folder named Data. From all 3 files i deleted the first row (where the file name was mentioend). Here's the M query
let
Source = Folder.Files("C:\Users\Ashish\Desktop\Data"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Data"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.PromoteHeaders([Data])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Month #", "ID", "Annual", "Quarterly", "Monthly", "Total", "Semi Annual "}, {"Month #", "ID", "Annual", "Quarterly", "Monthly", "Total", "Semi Annual "}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom1",{"Month #", "ID", "Annual", "Semi Annual ", "Quarterly", "Monthly", "Total"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Month #", Int64.Type}, {"ID", Int64.Type}, {"Annual", Int64.Type}, {"Semi Annual ", Int64.Type}, {"Quarterly", Int64.Type}, {"Monthly", Int64.Type}, {"Total", Int64.Type}})
in
#"Changed Type"The result is
Hi,
I tried but i could not solve it with that row remaining.
Thanks for trying and thanks for your time.