cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

union all tables using M with wildcard selection

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? 

5 REPLIES 5
Highlighted
Super User VI
Super User VI

Re: union all tables using M with wildcard selection

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Frequent Visitor

Re: union all tables using M with wildcard selection

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"

Highlighted
Frequent Visitor

Re: union all tables using M with wildcard selection

screenshot.JPG

 

Highlighted
Super User VI
Super User VI

Re: union all tables using M with wildcard selection

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Frequent Visitor

Re: union all tables using M with wildcard selection

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (691)