Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Irubataru
New Member

Working with tables inside of tables

I am fairly new to Power BI and I am currently exploring whether this is the right tool for me or not. Doing that I have quickly realised that it seems to be very hard to work with three- (or more) dimensional data. I.e. I have a table where one of the columns contains a table for each of the row. In these situations I find it very hard to work with this data and I was wondering what the correct syntax is for this if it is even possible.

 

Say for instance that I want to add a calculated column to a nested table for every row of my main data-table, how would I go about doing that?

 

To be slightly more specific, say I have a table where column "SubTable" is a tabular value which has columns "A" and "B", and I want to construct a column "C" = "A + B" for every row of my main table. I am in general just curious about these types of problems. Ideally I would also want to work with four- and five-dimensional data if that is possible. If I try to use the Power Query Editor it seems as if I can only edit the tabular value of a single row.

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

do you mean something like this?

let
    Source = #table({"SubTable"}, {{[A=1,B=11]},{[A=2,B=12]}}),
    #"Added Custom1" = Table.AddColumn(Source, "C", each [SubTable][A]+[SubTable][B])
in
    #"Added Custom1"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

I have the same type of issues it is a bit different. in my case, I import data from MongoDB which has nested tables in each record because of this whenever i import the data into power bi I am facing issues with this data format and building analytics on this data.

Stachu
Community Champion
Community Champion

do you mean something like this?

let
    Source = #table({"SubTable"}, {{[A=1,B=11]},{[A=2,B=12]}}),
    #"Added Custom1" = Table.AddColumn(Source, "C", each [SubTable][A]+[SubTable][B])
in
    #"Added Custom1"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thank you, yes that is basically what I was after.

 

In retrospect I have come to realise that this is not really the correct approach to take in Power BI, and that I should rethink my problem in terms of flat tables if possible. Although, I am also starting to think that this is not really the right tool for my problem.

Stachu
Community Champion
Community Champion

In order to consume the data you will need to put in flat tables, so with multiple dimensions it may not work out well
you can use Python & R in PowerBI though, so that may be a workaround



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.