Power Query - Column Data Types "Table", "Record", "List" - Why does it do this?

[rodneyc8063_1]

So Im starting out in Power BI and going through the Microsoft learning path for the DA-100 exam.

As Im going through the course, I noticed that in Power query sometimes I see a table with a column that is filled with the value of "Table"

Im instructed to "expand the column", which I do and it shows that there are a number of columns and values that show up, and get merged into the current table Im working with.

I was trying to google this and figure out - Why does it sometimes show in Power Query a column filled with values of "Table", or "Record" or "List"?

Just curious "why" does Power Query do this? Is it somp

[v-lionel-msft]

Hi @rodneyc8063_1 ,

This is structured values in Power Query. You can refer to the links.

Lists in Power Query – how, when and why 

Power Query: Structured Columns Deconstructed 

Basics of Value Structures in M – Power Query Formula Language 

Best regards,
Lionel Chen

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

[rodneyc8063_1]

Thanks to all so far for all the reading 🙂

So I can understand if its a JSON file, or a LIST of some type then that is why the column would show rows with only the value of "TABLE" or "LIST" but Im still a bit confused 

I went back to my training and it was in this lab here

https://docs.microsoft.com/en-us/learn/modules/clean-data-power-bi/8-lab

Specifically in the section "Configure the Product Query" - Step 5: Notice that the DimProductSubcategory column represents a related table (it contains Value links)

This is a screen shot of the PBI Power Query section

Now when I click on the "Value" under the DimProductSubcategory column it brings me to the following

I assume the "null" value is the value

But from here, how do I navigate back to the original "DimProduct" table?

I was able to go to "Applied Steps ->Source" and it brought me to here

Clicking on "Table" brings me here

And from here I click on the "Table" value for DimProduct and I am finally back to where I started

Not sure is this the right way to get back? Or did I take the long road?

Also, why would PBI even be showing these other tables, when I only imported 8 tables? Is it because PBI pulls in metadata on the entire schema?

Which leads me to another question Im confused about - Going back to the DimProduct table there are these 3 columns

FactInternetSales

FactProductInventory

FactResellerSales

All 3 of these columns are "Tables" - But only FactResellerSales was imported in. 

There are no relationships defined on the "Modelling" tab - So Im really not sure why the DimProduct table would even show these "related" columns/tables?

Since 2 out of 3 columns are tables that were not even imported in (and in my head dont exist in this file since it was not imported in), and theres not even any relationships defined in the modelling tab - Why/How does PBI know that these 3 other columns are even from "related" tables?

Any further insight or advice would be very much appreciated!

[amitchandak]

@rodneyc8063_1 , when you bring json, XML data. These data are data inside data. Flat format hierarchies. So there is a table inside table.  MongoDB and other document DB will also behave like that.

Power BI needs Data in a tabular format, not the hierarchical format. So it Shows expand icon.

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

Expand Icon is also shown when you merge tables so that you make sure the correct granularity while expending.

Way do is same as explained here

https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...

[Greg_Deckler]

@rodneyc8063_1 - Society of Mining Professors?

Anyway, the reason is that what is being returned as a column is not a single value but rather either a table of values, a list of values or a record. Not sure what else to say about it. Perhaps think of it as HTML/XML, you have something simple like:

<Title>DAX Cookbook</Title>
<Author>Greg Deckler</Author>
<Chapters>
  <Chapter1>Thinking in DAX</Chapter1>
  <Chapter2>Dealing with Dates and Calendars</Chapter2>
  <Chapter3>Tangling with Time and Duration</Chapter3>
</Chapters>

So, looking at this, Title and Author can become columns with single values but Chapters is really a list of 3 values. So, you need to expand that if you want to see all of the values, otherwise, all Power Query can tell you is that at the highest level of the hierarchy, this elements holds some list of values.