Forum Discussion

rodneyc8063_1's avatar
rodneyc8063_1
Icon for Helper V rankHelper V
6 years ago

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

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

Need Help

4 Replies

    • rodneyc8063_1's avatar
      rodneyc8063_1
      Icon for Helper V rankHelper V
      6 years ago

      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!

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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=EAIaIQobChMI7Za92YSi6wIVFSQrCh0vDgIgEAAYASAAEgLd5_D_BwE