Results 1 to 5 of 5

Thread: How to append tables based on naming convention?

  1. #1
    Neophyte czhang's Avatar
    Join Date
    May 2021
    Posts
    3
    Articles
    0
    Excel Version
    MS365

    How to append tables based on naming convention?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I'm very new to M code. Any help would be appreciated!

    I have many tables with consistent naming convention and I need to append all of them. It's tedious to append them one by one manually through UI. I tried to look up but no one seems to have the same issue. This sounds like easy but I really couldn't get it work. Below is my code that I attempted to use but it keeps giving me error.

    Simply put, I want to append all the tables that are already loaded in query with table name starts with "Test_".

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Test_Table14"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"Item", type text}, {"Name", type text}, {"GL", Int64.Type}, {"Stream", type text}, {"Project", type text}, {"Status", type text}}),
        #"Appended Query" = Table.Combine({#"Changed Type", [Name = Text.StartsWith ("Test_")]})
    in
        #"Appended Query"
    Thank you!

  2. #2
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    This can be done easily if the tables to be imported are formatted as tables.

    PHP Code:
    let
        Source 
    Excel.CurrentWorkbook(),
        
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Test_")),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Month", "Value"}, {"Month", "Value"})
    in
        
    #"Expanded {0}" 
    For this example, my tables have the column titles Month and Values.

    Otherwise you have to import from the saved workbook, because then also normal data tables will be imported. That means you can import from the current workbook from the last saved state.
    Last edited by pinarello; 2021-05-13 at 01:33 AM.

  3. #3
    Neophyte czhang's Avatar
    Join Date
    May 2021
    Posts
    3
    Articles
    0
    Excel Version
    MS365
    This worked very well, thank you so much!
    Just out of curiosity, what does "Content" mean? Is it for telling query to look through content for the given columns?


  4. #4
    Neophyte czhang's Avatar
    Join Date
    May 2021
    Posts
    3
    Articles
    0
    Excel Version
    MS365
    This worked very well, thank you so much!
    Just out of curiosity, what does "Content" mean? Is it for telling query to look through content for the given columns?

  5. #5
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    Table is shown in the "Content" column. In this respect, by expanding (pressing the button with the arrows pointing to the right and left), the contents of the corresponding tables are shown

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •