none
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:

    let
        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)),

    in
        #"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

Answers

  • This function should do the trick:

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


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    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
    Moderator