Forum Discussion

smpa01's avatar
smpa01
Community Champion
8 years ago
Solved

Dynamically create column if a particular column does not exist within the data

Hi,   I receive numerous excel files each month which I need to consolidate. I use Excel.Workbook to do that.   However, a particular column "Semi Annual" may or may not present in those files. ...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    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