union all tables using M with wildcard selection

[Anonymous]

Hi 

I have managed to automate the import of multiple tabs in an Excel spreadsheet that prefix with 'Date' 🙂

let
      Source = Excel.Workbook(File.Contents("C:\Users\mbo06\OneDrive - MyPharma\MMCL - PowerBI\ForecastvsBudget - PowerBI.xlsx"), null, true),
     #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Date"))
in
     #"Filtered Rows"

Now, I need to union all tables together do that I see all data columns for all of the tables. Can anyone help with M functions? 

[edhans]

Just use Table.Combine().

Table.Combine(Table1, Table2, #"Some other table name", Table4)

That will append 4 tables into one. All columns will be visible from all tables. Any columns in one table not in another will result in null showing up in the other tables.

By default Table.Combine is Union All. To get a normal union, you'd need to follow up with Table.Distinct().

The Combine operation is what you might want to use, but it is only designed to combine identical tables. It doesn't handle different tables with different columns. 

If you need more help, please share some data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Thanks for getting back to me. How do I union all using a wildcard for 'Date'?

I tried this and it did not work

let
      Source = Excel.Workbook(File.Contents("C:\Users\mbo06\OneDrive - MyPharma\MMCL - PowerBI\ForecastvsBudget -              PowerBI.xlsx"), null, true),
     #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Date"))
     ,Table.Combine= ( each Text.StartsWith([Item], "Date"))
in
#"Filtered Rows"

[Anonymous]

[edhans]

Again, if you need further help, please provided sample data. Otherwise I am just guessing as to what will work and we will go back and forth.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Sorry - I count see an option to share files / data. Are you able to access here : https://github.com/ametsanalytics/ExcelFiles