cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Recoba88 Member
Member

Connect power query to name range in other workbook

Hi,

 

I want to load to power query table from other workbook. I defined  range for this table with formula (offset) - How I connect it to Power query ?

 

 

Thanks,

 

Igor

 

10 REPLIES 10
Recoba88 Member
Member

Connect Power query with range from another workbook

Hi,

 

If I use a formula in the range like offset . I can't bring this range to power query.

 

Help me

Super User
Super User

Re: Connect Power query with range from another workbook

Can you provide some more detail around this?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Recoba88 Member
Member

Re: Connect Power query with range from another workbook

In the one hand you can see the whole ranges in my workbook that power query represents 

 

In the other hand you can see DATA_DRISHOT_BL - with offset formula that power query don't represent to me

 

Any Idea?

 

 

CHECK3.PNGCHECK4.PNG

Super User
Super User

Re: Connect power query to name range in other workbook

You can't.

 

You can only connect to a named range in the current workbook.

 

You can connect to tables and worksheets in other workbooks.

 

My advice would be to convert the named range to a real table.

Specializing in Power Query Formula Language (M)
Recoba88 Member
Member

Re: Connect power query to name range in other workbook

I can connect to deifined range in other book if this range doesn't include formulas.

 

I can't use your suggestion cause  the number of rows in  table will change time by time and I want to bring only rows with data , not null rows

Super User
Super User

Re: Connect power query to name range in other workbook

You are right with your first remark: it is possible to connect to a fixed range in another workbook.

 

I don't understand your second remark as table sizes are automatically adjusted for new and deleted data.

 

Specializing in Power Query Formula Language (M)
Recoba88 Member
Member

Re: Connect power query to name range in other workbook

I don't want that power query will bring this data and then filter null rows for example

 

column1  column 2

 

1               aa

2               bb

3               cc

4

5

 

 

rows 4 and 5 for me are null rows beacuse column 2 is empty. If I use table and bring it to power query I should filter the null rows. 

 

I want to avoid using filter and bring only the 3 first rows to powe query (I wanted to do it with range and offset formula)

 

 

Super User
Super User

Re: Connect power query to name range in other workbook

Ah, I understand this is a follow up on your previous question about importing data from 450 workbooks * 5 sheets each?

 

It is clear what you want, but dynamic ranges will not work for technical reasons (as explained).

 

You might consider VBA to create fixed ranges from dynamic ranges, which would not be my preference,

 

In my view, the way to go is to flter out the nulls in Power Query.

I agree, it is not very simple from multiple workbooks with multiple sheets.

Coincidentally, I created a video recentely (for another topic on this forum) to illustrate how this can be done.

For your case, you can define the filtering in the Sample Transform Query (in the video between 1:50 - 2:10), only "use first rows as headers"  is defined as transform step; here you might add filtering out the nulls).

 

With this solution, nulls will be filtered out before the tables are expanded, which is quite similar to filtering out rows at the source.

Specializing in Power Query Formula Language (M)
Moderator Eric_Zhang
Moderator

Re: Connect Power query with range from another workbook


@Recoba88 wrote:

In the one hand you can see the whole ranges in my workbook that power query represents 

 

In the other hand you can see DATA_DRISHOT_BL - with offset formula that power query don't represent to me

 

Any Idea?

 

 

CHECK3.PNGCHECK4.PNG


There's no such "OFFSET" in Power BI Desktop. If you could post any sample data and expected output, we can try to simulate it in Power Query.