Connect power query to name range in other workbook

[Recoba88]

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

[ShilajitBanerje]

Hi,

You can define a custom function and use the below M Query if you know the location of the Range and its Sheet.

Then using the below, you can retrieve the data inside that Range in that Sheet in one shot.

let
Source = (Parameter1 as binary) =>
let
Source = Excel.Workbook(Parameter1, null, true),
Filtered_Rows = Table.SelectRows(Source, each ([Name] = <Name Of The Sheet>))[Data]{0}[<Column Of the Cell>]{Row of the Cell -1}
in
Filtered_Rows
in
Source

[MarcelBeug]

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.

[Recoba88]

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

[MarcelBeug]

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.

[Recoba88]

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)

[MarcelBeug]

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.

[Recoba88]

Hi,

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

Help me

[Greg_Deckler]

Can you provide some more detail around this?

[Recoba88]

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?

[Eric_Zhang]

---

@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?

---

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.

[MarcelBeug]

It looks like this question is continued in a new topic?