OFFSET function with multiple table

[xdenama]

I have 100++ sheet (table) with same format with difference high (total row not fix, will change). How to combine all table (sheet) in one sheet only. I prefer OFFSET function, any other solution is welcome. TQSM

 

[xdenama]

here is the way for

example:

let
    Source = Excel.CurrentWorkbook(),
    Expand = Table.ExpandTableColumn(Source, "Content", {"Week 1", "Week 2", "Week 3", "Year", "Month", "Day"}, {"Week 1", "Week 2", "Week 3", "Year", "Month", "Day"}),
    Filter = Table.SelectRows(Expand, each not Text.Contains([Name], "Query"))
in
    Filter

View attachment 22082
and so on...

Actually, all table from online data, unfortunately, the result is shown below, only number, not other details....

 

[xdenama]

read post#6

I assumed
- all tables are Excel Tables, not ranges that pretend to be tables
- there is nothing more than these tables

btw. you didn't update your profile about Excel version, can you do that?

My Excel is OFFICE 360

 

[sandy666]

My Excel is OFFICE 360

365 rather ok, but do that in your profile

Actually, all table from online data, unfortunately, the result is shown below, only number, not other details....

post an example of your table (with generic data
you said your tables have the same structure (headers are the same and number of columns are the same)
example of the code is for MY tables , not yours

 

[sandy666]

thanks for profile update

 

[xdenama]

I'm still thinking how to provide a table with generic data, because all data from online, real data. I try copy paste value, but there is not headers. Sorry about the headers, headers is from multiple department, but the total column is same...

 

[sandy666]

Excel Table has header(s), always
if there is no headers but you'll use Ctrl+T headers will be generated as Column1 , Column2 , etc

make a copy of your table (?) , change data if necessary , use XL2BB to post this example here (few rows only)

 

[sandy666]

do everything on copy of your original file!
​

here is example file: s666-PQ-join tables.xlsx

 

[xdenama]

I upload image in Power Query, only first table is shown, second table is "null".

 

[sandy666]

I'm saying one thing you did totally different thing (forget about video!)

so
1. New Query - From Other Sources - Blank Query
2. go to Advanced Editor and replace all there with this:

let
    Source = Excel.CurrentWorkbook()
in
    Source

you should see something like this

then expand content

 

[xdenama]

I upload image in Power Query, only first table is shown, second table is "null".

Same result like above...