Dynamic table data visible in query editor but blank in data view

Question

I've been struggling with a problem in PowerBI that at first glance should be easy and straightforward, but seems to be quite the contrary.

In short, I have some tables that I'm referencing dynamically, in order to expand their columns and do additional processing. When I'm building in the Query Editor, everything seems to be fine. The preview data in the editor is showing, and is correct. However, once I click Close and Apply, the data view in PowerBI does not show those referenced tables, but the cells seem to be empty.

Now, the details.
My tables are these:

Table shops contains 3 records that we'll use to dynamically reference the actual tables shop_thehouse, shop_goldforex and shop_goldline.

Table shops_all is based on table shops and adds a column that dynamically references the other tables.

let
    Source = shops,
    AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("shop_" & Text.From([shop]), #sections[Section1]))
in
    AddedTables

Table shops_all shows like this in the editor, and contains the correct data, which means the dynamic referencing works fine.

However, when clicking Close & Apply, we see this in the Data View. The Data column is empty.
If it were not empty, it would show [Table] in the cell.

I then tried to add another column to shops_all, but this time with a static reference to one of the base tables.

let
    Source = shops,
    AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("shop_" & Text.From([shop]), #sections[Section1])),
    AddedFixedReference = Table.AddColumn(AddedTables, "FixedRef", each shop_thehouse)
in
    AddedFixedReference

Now each cell in the FixedRef column contains the data of shop_thehouse. So far so good.

After clicking Apply & Close, we now see this in the Data View

As expected, column FixedRef shows [Table] in each cell.
But - and this was unexpected to me - we now also see that the record for shop thehouse also shows [Table] in the Data column, which contains the dynamic reference.

I tried adding another FixedRef2 column that contains a fixed reference to another base table, and that shows an additional [Table] in the corresponding cell.

let
    Source = shops,
    AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("shop_" & Text.From([shop]), #sections[Section1])),
    AddedFixedReference = Table.AddColumn(AddedTables, "FixedRef", each shop_thehouse),
    AddedFixedReference2 = Table.AddColumn(AddedFixedReference, "FixedRef2", each shop_goldforex)
in
    AddedFixedReference2

How is this possible?
I completely do not understand this.
How do I get the dynamic reference to show [Table] in the Data View, without having to add the FixedRef columns?

btw, table shops_all has Enable load checked, so that's probably not it.

ps: I'm using the June 2022 version of PowerBI Desktop.

[Edit 20 jun 2022]
Because most reactions say that I should just expand the table, I'm going to post some screenshots proving that this is not the solution. As you can see, after expanding the table in the Query Editor, everything looks fine. The correct data is shown.

let
    Source = shops,
    AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("shop_" & Text.From([shop]), #sections[Section1])),
    ExpandedData = Table.ExpandTableColumn(AddedTables, "Data", {"description", "bid", "ask"}, {"description", "bid", "ask"})
in
    ExpandedData

However, when I click Close and Apply, the Data View shows up blank (same after refresh):

This is the reason as to why I didn't show this in my original post.
It's because I know for a fact that the issue must be in the AddedTables step, which is yielding blank or null objects in the Data View.
This in contradiction to the Query Editor where the AddedTables step does not return blank/null and clearly show a [Table] object which contains the correct data.
For some reason unknown to me, it works in the Query Editor, but not in the Data View.

[Edit 20 jun 2022 part 2]

As requested, sharing the .pbix file through Google Drive
StackOverflow_Question_72679896.zip

You'll have to adjust the Path parameter to point to the correct directory, so the accompanying .xls can be found.

This is logical because your cell (Data) holds a complete table and when you click on it it shows the data (you did this for all 3 sets). However when you Click apply, you cannot expect that PBI magically shows you that data. You will need to expand the column "Data" to be able to return (and view) the data in PBI. So add the step and you should be ok. — Aldert
– Aldert, Commented Jun 19, 2022 at 20:12
I already did that extra step. Nothing shows. The data tables in the cells are really empty or null. — Bjorn Mistiaen
– Bjorn Mistiaen, Commented Jun 19, 2022 at 20:27
From your example I cannot see this. I can even read you are getting a table based on your example... Did you click on the 2 arrows on your data column? — Aldert
– Aldert, Commented Jun 19, 2022 at 20:33
Yes, I did click the 2 arrows to expand the table, but since the data didn't show up in the Data View, I deleted that step again, because clearly the issue is caused by the cellls containing no Table object. I'll add screenshots of the table expand step tomorrow to show you. — Bjorn Mistiaen
– Bjorn Mistiaen, Commented Jun 19, 2022 at 20:51
Added section [Edit 20 jun 2022] to clarify that expanding the table in the Data column is not the problem. — Bjorn Mistiaen
– Bjorn Mistiaen, Commented Jun 20, 2022 at 7:16

davidebacci · Accepted Answer · 2022-06-20 10:33:46Z

1

This code works for me:

let
    Source = shops,
    AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("shop_" & Text.From([shop]), [shop_thehouse =shop_thehouse, shop_goldforex=shop_goldforex, shop_goldline = shop_goldline ])),
    #"Expanded Data" = Table.ExpandTableColumn(AddedTables, "Data", {"description", "bid", "ask"}, {"Data.description", "Data.bid", "Data.ask"})
in
    #"Expanded Data"

Full credit to this excellent resource.

https://ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-3/

answered Jun 20, 2022 at 10:33

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bjorn Mistiaen Over a year ago

I agree, this works. This was the reason I included #sections[Section1] as environment in my code (also tried #shared) but it didn't work. Your solution works by explicitly pulling the objects into the inner environment. But I was trying to create a dynamic solution, which means if I add another shop_* table, it would automatically be included. With your current solution, it still needs an update of the code. Do you think there's another way to do this? It's not a must-have to use Expression.Evaluate(). I just used that because I didn't see another way. If not, I'll accept this answer.

Bjorn Mistiaen Over a year ago

This should work, but it doesn't. Not sure why. AddedTables = Table.AddColumn(Source, "Data", each Expression.Evaluate("shop_" & Text.From([shop]), #shared))

davidebacci Over a year ago

I can't think of anything off the top of my head. Even if it were dynamic and you wanted to add another shop_, you would still have to change the code elsewhere so this would just be a complementary change to keep everything in sync. It might be worth posting a separate question on whether this is the best approach as I'm honestly not sure.

davidebacci · Accepted Answer · 2022-06-19 21:29:34Z

1

You need to click these arrows to expand the tables. PowerBI doesn't understand this datatype; they only exist in Power Query and need to be expanded.

answered Jun 19, 2022 at 21:29

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

7 Comments

Bjorn Mistiaen Over a year ago

Added section [Edit 20 jun 2022] to clarify that expanding the table in the Data column is not the problem.

davidebacci Over a year ago

Any chance you can share your .pbix?

davidebacci Over a year ago

Why do you use Expression.Evaluate() out of interest? Have you seen the environment parameter complications noted here: blog.crossjoin.co.uk/2015/02/06/…

Bjorn Mistiaen Over a year ago

Added [Edit 20 jun 2022 part 2] section with .pbix file

Bjorn Mistiaen Over a year ago

I'm aware of that blog post. I'm using Expression.Evaluate() to dynamically add the "shop_" string to the value in the column. So together, it forms the name of the table I need, and Expression.Evaluate() should then return said table.

|

Collectives™ on Stack Overflow

Dynamic table data visible in query editor but blank in data view

2 Answers 2

3 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related