when using #sections powerbi gives empty table

[Aklys]

Too long a story to state why I'm using #sections but there is a need due to complexity in data sources being utilised. So any queries about why it is needed can be left at the door.

The question is to understand the behaviour of powerbi returning an empty table when powerquery editor doesn't. I've got a sample of the situation that the following link can demonstrate https://we.tl/t-71RJFDqY4Y.

I am ultimately needing to reference an existing query for which I am using the #sections reference. It works completely fine in powerquery editor but I find when I close and apply the editor PowerBI for some reason returns an empty table (well empty in this situation if I try other aspects it returns errors which you'll understand why as you read further).

On further assessment I have discovered this is primarily due to something occuring when I try to extract the value from #sections. It seems the process the editor goes through is different to the process that PowerBI goes through. If I was to slightly adjust the process I have in the example above it would output an error of not knowing the column name referenced or that it can't convert null into a table.

My question is, does anyone know a work around for how to get the #sections referencing to work in PowerBI?

Thanks in advance for any direction.

[Aklys]

Unfortunately I want to reference existing query results dynamically but that creates an issue with the privacy firewall when the source of the dynamic references is from a CSV. It doesn't seem to like combining the data inside powerquery without utilising #sections.

I guess that PowerQuery can only do dynamic referencing in it's editor but breaks when you leave that environment. Thanks for clarifying the ephemeral nature of #sections

[v-zhengdxu-msft · Accepted solution]

Hi @Aklys 

Here is the description of the #sections:

#section only works in the compile environment, and when you leave Power Query, it means that it is out of the running state, so it returns a null value.

M Language Sections - PowerQuery M | Microsoft Learn

Instead of relying on #sections, try to use named queries directly. This can bypass the issues caused by the different processing stages in Power BI.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.