OFFSET function with multiple table

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
here is the way for

example:
Power Query:
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....
 

Attachments

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,253
My Excel is OFFICE 360
365 rather :cool: 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
 

xdenama

New Member
Joined
Feb 12, 2016
Messages
39
Office Version
365
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,253
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)

tblvsrange.png
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,253
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:
Power Query:
let
    Source = Excel.CurrentWorkbook()
in
    Source
you should see something like this
content.png

then expand content
expand.png
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,858
Messages
5,507,763
Members
408,648
Latest member
gazmoz17

This Week's Hot Topics

Top