How to append tables based on naming convention?

czhang

New member
Joined
May 12, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
MS365
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!
 
This can be done easily if the tables to be imported are formatted as tables.

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

:yo:
 
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?
 
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
 
Back
Top