Merging from folder - files has a different number of columns RRS feed

  • Question

  • Hi,

    I need an advice - I have a folder with some files, which each contains columns with same names, but two of them has for example two columns more. And in future is possible, that there will be some file with maybe five more columns.

    There is example of my code:

        Zdroj = Folder.Files("D:\data-win\plocha\keis\data\hist"),
        Deleted_columns_only_Content_left = Table.SelectColumns(Zdroj,{"Content"}),
        Added_column_Get = Table.AddColumn(Deleted_columns_only_Content_left, "Get", each Excel.Workbook([Content])),
        Deleted_column_Content = Table.RemoveColumns(Added_column_Get,{"Content"}),
        Expanded_Get = Table.ExpandTableColumn(Deleted_column_Content, "Get", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Get.Name", "Get.Data", "Get.Item", "Get.Kind", "Get.Hidden"}),
        Added_Column_Nohead = Table.AddColumn(Expanded_Get, "Nohead", each Table.PromoteHeaders([Get.Data])),
        Deleted_Columns_except_Nohead = Table.SelectColumns(Added_Column_Nohead,{"Nohead"}),
        Expanded_Nohead = Table.ExpandTableColumn(Deleted_Columns_except_Nohead, "Nohead", Table.ColumnNames(Table)),

        #"Změněný typ"

    So I have to set a command to expand "Nohead" but I dont know names of columns in used tables. Can you help me with bolded command line, please? 

    Friday, November 24, 2017 11:26 PM


  • This function should do the trick:

     (Table as table, ColumnName as text) =>
        fnExpandAllColumns = Table.ExpandTableColumn(Table, ColumnName, Table.ColumnNames(Table.Combine(Table.Column(Table, ColumnName))))

    Imke Feldmann - MVP Data Platform (PowerBI) - 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, November 25, 2017 5:45 PM